Using CountIf with 2 conditions - help!

[Sarah]

Hi there
I have 2 columns of data. I want to ask Excel to look in column 1, determine
which rows match the condition, then look at column 2 and in those rows which
matched the condition, count how many have values in them i.e. exclude
blanks. Not sure how to build 2 conditions into a countif - any ideas??
Thanks!

 

[Marcelo]

Sarah,

you could cancatenate the both columns in another one and countif this new.
just a tip with an alternative, while we do not able to find another way.

regards
Marcelo - Brazil


"Sarah" escreveu:

 

[Max]

I have 2 columns of data. I want to ask Excel to look in column 1, determine
which rows match the condition, then look at column 2 and in those rows which
matched the condition, count how many have values in them i.e. exclude
blanks. Not sure how to build 2 conditions into a countif - any ideas??

Use SUMPRODUCT ..

Assuming your 2 cols are cols B and D,
where col B houses eg: Name1, Name2, etc
and col D may or may not contain figures

With E1, E2 containing: Name1, Name2, ...
we could put in say, F1:
=SUMPRODUCT(($B$1:$B$100=E1)*($D$1:$D$100<>""))
and copy down

Adapt the ranges to suit, but note that we can't use entire col references
(eg: A:A, B:B) in SUMPRODUCT

 

[Larry S]

Use an Array Formula. The following is an example of looking at 2 columns of
data:

=COUNT(IF((A1:A5=1)*(D15=2),1))

This is evaluating A1:A5 to see if it contains a "1" and (consider the "*"
an "AND") evaluating D15 to see if it contains a "2". When BOTH conditions
are met, the counter is incremented by 1. A couple notes:

- You cannot specify $A:$A, as the range - that is, the whole "infinite"
column cannot be specificed, rather you must specify a finite range.
- Upon entering this command, when you are ready to hit the "Enter" key,
hold down the Ctrl-Shift keys first. This will result in brackets being
place around the formula.

Hope this helps.

 

[trempnvt]

Hi Sarah,

The SUMPRODUCT function should work for this.

Try something like

=SUMPRODUCT((condition one)*(condition two))

where, for example, condition one is A1:A50<200 and condition two i
C1:C50="Yes".

Good luck

 

[Sarah]

Thanks SO much! How did I not not know about SUMPRODUCT before???

 

[Max]

Thanks SO much! How did I not not know about SUMPRODUCT before???

You're welcome, Sarah !

Try Bob Phillips' excellent treatment on SUMPRODUCT at his:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Scroll down to "SUMPRODUCT Explained" where you'll find all the details
fully explained. And if you do set aside the time to look through the entire
white paper, you'd well be on your way to mastering it very quickly !