Compare ranges and count

Solved/Closed
Report
Posts
5
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 9, 2010
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
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



7 replies

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Hi,

Try with this formula :

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

Best regards
Posts
5
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 9, 2010

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?
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Try this :
=SUMPRODUCT(((C6:C106=1)*(T6:T106>=0))*(T6:T106))
Posts
5
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 9, 2010

No that just gets #VALUE! I think it might be something like =SUMPRODUCT((C6:C106=1)*IF(T6:T106>=0,SUM????,0))
Posts
5
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 9, 2010

Might be more like this, =IF(C6:C105=2,SUMIF(T6:T105>0,????,""),"")
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Hi,

I don't understand the formula works for me.
Posts
5
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 9, 2010

I have Excell 2007 this maybe why it doesn't work but this does =SUMIFS(T6:T105,C6:C105,1,T6:T105,">0")
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Hi,

And I have excel 2003, maybe you are right and the formula doesn't work under excel 2007.

Thanks for having shared your solution.

See you :)