Compare ranges and count

Solved/Closed

ccpsc Posts 5 Registration date Wednesday September 8, 2010 Status Member Last seen September 9, 2010 - Sep 8, 2010 at 06:24 AM
aquarelle Posts 7181 Registration date Saturday April 7, 2007 Status Moderator Last seen July 18, 2025 - Sep 9, 2010 at 04:38 PM

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

Compare ranges and count
"Compare versions word" - Guide
Beyond compare download - Download - File management
Excel count occurrences - Guide
Count names in excel - Guide
Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as named ranges. ✓ - Excel Forum

7 responses

Answer 1 / 7

aquarelle Posts 7181 Registration date Saturday April 7, 2007 Status Moderator Last seen July 18, 2025 491
Sep 8, 2010 at 05:28 PM

Hi,

Try with this formula :

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

Best regards

Answer 2 / 7

ccpsc Posts 5 Registration date Wednesday September 8, 2010 Status Member Last seen September 9, 2010
Sep 8, 2010 at 05:50 PM

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?

Answer 3 / 7

aquarelle Posts 7181 Registration date Saturday April 7, 2007 Status Moderator Last seen July 18, 2025 491
Sep 8, 2010 at 06:04 PM

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

Answer 4 / 7

ccpsc Posts 5 Registration date Wednesday September 8, 2010 Status Member Last seen September 9, 2010
Sep 8, 2010 at 06:54 PM

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

Answer 5 / 7

ccpsc Posts 5 Registration date Wednesday September 8, 2010 Status Member Last seen September 9, 2010
Sep 8, 2010 at 07:26 PM

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

Answer 6 / 7

aquarelle Posts 7181 Registration date Saturday April 7, 2007 Status Moderator Last seen July 18, 2025 491
Sep 9, 2010 at 06:39 AM

Hi,

I don't understand the formula works for me.

Answer 7 / 7

ccpsc Posts 5 Registration date Wednesday September 8, 2010 Status Member Last seen September 9, 2010
Sep 9, 2010 at 04:15 PM

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

aquarelle Posts 7181 Registration date Saturday April 7, 2007 Status Moderator Last seen July 18, 2025 491
Sep 9, 2010 at 04:38 PM

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 :)

Similar discussions

Macro to compare sheets(Jan & Feb), highlight the difference, copy the comme

How to compare two different columns

compare and count

Compare Fields

How to Compare two workbooks and see if data has moved or not?

Similar discussions

Subscribe To Our Newsletter!