Countif for multiple criteria given two dynamic ranges match

[grexcelman]

I'm trying to count the instances value are less than zero given two dynamic column ranges match. That's a mouthful. These are named ranges 'universe' and 'planets'. I have the following formula to give me the total number of instances that I have a match however I'm not sure where to include the additional criteria to return how many of these matches have corresponding data values less than 0 in the adjacent column C.

  {=COUNT(IF(ISNUMBER(MATCH(universe,planets,0)),$C$7:$C$35))}

Thanks in advance!

 

[oldbrewer]

I have never seen this use of match - I assume you have tried c7:c35 <0

or wrap it in a sumproduct where c7:c35=0 I will have a play with this in about an hour if nobody has sorted it....

 

[Aladin Akyurek]

{=COUNT(IF(ISNUMBER(MATCH(universe,planets,0)),IF($C$7:$C$35 < 0, $C$7:$C$35)))}

Or...

{=SUM(IF(ISNUMBER(MATCH(universe,planets,0)),IF($C$7:$C$35 < 0, 1)))}

 

[oldbrewer]

1	2	3
2	3	4
3	4	5
4	4	6
5	6	7
6	7	8
7	7	0	####
8	9	10				2
9	10	11
10	11	12				=SUMPRODUCT(($A$1:$A$20=$B$1:$B$20)*($C$1:$C$20=0))
11	11	13
12	13	14				even better
13	14	15				2
14	15	16
15	15	0	####			=SUMPRODUCT((range1=range2)*(range3=0))
16	17	18
17	18	19
18	19	20
19	20	21
20	21	22				the 3 columns are of course range1, range2 and range3

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[grexcelman]

{=COUNT(IF(ISNUMBER(MATCH(universe,planets,0)),IF($C$7:$C$35 < 0, $C$7:$C$35)))}

Or...

{=SUM(IF(ISNUMBER(MATCH(universe,planets,0)),IF($C$7:$C$35 < 0, 1)))}

Both worked nicely! thanks very much.

 

[grexcelman]

getting a bunch of #N/A results with the sumproduct and combining '*' method. Will need to review how to incorporate but I think the dynamic ranges might be throwing this off. Thanks again.

 

[AliGW]

Is it because the dynamic ranges are of unequal sizes?

 

[Aladin Akyurek]

Both worked nicely! thanks very much.

You are welcome.

getting a bunch of #N/A results with the sumproduct and combining '*' method. Will need to review how to incorporate but I think the dynamic ranges might be throwing this off. Thanks again.

If you insist on SumProduct...

=SUMPRODUCT(--ISNUMBER(MATCH(universe,planets,0)),--($C$7:$C$35 < 0))

where the size of universe and the size of C7:C35 (number of cells of both range) must be equal. This requirement holds also for SUM+IF and COUNT+IF versions equally.

 

[oldbrewer]

1	2	2
2	3	1
3	4	0
4	4	-1	<
5	6	-2
6	7	2
7	7	1		3
8	9	0		=SUMPRODUCT((a=b)*(d<1))
9	10	-1
10	10	-2	<
11	12	2
12	13	1
13	14	0
14	14	-1	<	the #'s represent blanks
#	#	#		to show how a dynamic range can be accomodated
#	#	#
#	#	#
#	#	#		a, b and d are the 3 columns
#	#	#
#	#	#

<colgroup><col><col><col><col span="7"></colgroup><tbody>
</tbody>