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!

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:

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!

"Sarah" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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

This is evaluating A1:A5 to see if it contains a "1" and (consider the "*"
an "AND") evaluating D1:D5 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.


"Sarah" wrote in message
...
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!

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 is
C1:C50="Yes".

Good luck!


--
trempnvt
------------------------------------------------------------------------
trempnvt's Profile: http://www.excelforum.com/member.php...o&userid=34710
View this thread: http://www.excelforum.com/showthread...hreadid=544795

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

"Max" wrote:

"Sarah" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Sarah" wrote:
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 !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---