Previous Topic Next Topic

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 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 - 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



Related:

7 responses

Answer 1 / 7
aquarelle Posts 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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 7141 Registration date Saturday April 7, 2007 Status Moderator Last seen December 19, 2024 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

Counting cells containing only numbers
jutulu -
jutulu -

4 responses
Excel Macro/Code help: Creating sheet for each name in column of another sheet
excelnovice2019 -
vcoolio -

21 responses
Copy range data in a column to new column.
anthonyishere -
anthonyishere -

6 responses
how to count sentences in a paragraph..." using c language
gealon2024 -
indhu -

4 responses
WinMerge - How to compare the content of two PDF documents?
Isasis -
ac3mark -

1 response