Compare ranges and count Solved/Closed

Question

Hello,  

I have two ranges of cells C6:C106 and T6:T106. In the C range I have a 1, 2 or 3 in the T range I have positive or negative numbers. I need a formula to count the number of positive numbers there are in the T range when there is a 1 in the C range.  

Eg; There are six 1's in the C range. In corresponding cells in the T range there are four positive and two negative numbers.  I need a formula which returns 4. 

1     15
3    -21
1     87
2     52
1    -87
2     22
1     55
1    -37
3     77
1     48



Configuration: Windows 7 / Firefox 3.6.8

aquarelle · Answer

Hi,

Try with this formula :

=SUMPRODUCT((C6:C106=1)*(T6:T106>=0))

Best regards

ccpsc · Answer

Thanks aquarelle that worked fine. Now I would like to sum those positive numbers which comes to 205. Can you modify it to do that?

aquarelle · Answer

Try this :
=SUMPRODUCT(((C6:C106=1)*(T6:T106>=0))*(T6:T106))

ccpsc · Answer

No that just gets #VALUE! I think it might be something like =SUMPRODUCT((C6:C106=1)*IF(T6:T106>=0,SUM????,0))

ccpsc · Answer

Might be more like this, =IF(C6:C105=2,SUMIF(T6:T105>0,????,""),"")

aquarelle · Answer

Hi,

I don't understand the formula works for me.

ccpsc · Answer

I have Excell 2007 this maybe why it doesn't work but this does =SUMIFS(T6:T105,C6:C105,1,T6:T105,">0")

Compare ranges and count

7 responses

Similar discussions

Subscribe To Our Newsletter!