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 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 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



7 responses

Answer 1 / 7
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Sep 8, 2010 at 05:28 PM
Hi,

Try with this formula :

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

Best regards
0
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?
0
Answer 3 / 7
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Sep 8, 2010 at 06:04 PM
Try this :
=SUMPRODUCT(((C6:C106=1)*(T6:T106>=0))*(T6:T106))
0
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))
0

Didn't find the answer you are looking for?

Ask a question
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,????,""),"")
0
Answer 6 / 7
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Sep 9, 2010 at 06:39 AM
Hi,

I don't understand the formula works for me.
0
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")
0
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 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 :)
0

Similar discussions

Counting cells containing only numbers
jutulu -
jutulu -

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

4 responses
Comparing Two Excel Sheets and copying like data to third sheet
coz2992 -
TrowaD -

11 responses
Comparing columns and returning value
ll -
kittu -

35 responses
VBA Compare 2 sheets and output difference to 3rd sheet
jmah -
TrowaD -

3 responses