View Single Post
#5
April 21st 09, 01:44 AM posted to microsoft.public.excel.worksheet.functions
 Fred Smith[_4_] external usenet poster First recorded activity by ExcelBanter: Jan 2008 Posts: 2,389
Count If Column A = x and Column B = y

There's no reason Sumproduct won't work in this situation.

You should spend some time getting to know it, as it's a very useful
function. It's worth every minute you spend on it.

By the way, the pluses in your formulae are superfluous.
=S3&YEAR(A3)&MONTH(A3)
works just as well, and will be easier for others to understand.

Regards,
Fred.

"Altair1972m" wrote in message
...

"Teethless mama" wrote:

=SUMPRODUCT((A1:A100="x")*(B1:B100="y"))

"Trish" wrote:

I am trying to use the count function where two conditions in different
columns need to be satisfied and having no luck. Basically I want to
look in
the first column to see if it's A, then look in 2nd column to see if
it's B,
then count it... is that possible?

I had this same question earlier in the year. I just tried to plug in the
sumproduct command and it didn't work for me.

I'm going to give you a much longer method of doing it which was my work
around.

I had 4 categories to marry:
Status of Client
Date of Arrival
VOLAG
County

And what I was doing was counting all clients arriving during a month from
a
volag in certain counties on a separate page.

What I did was create some columns off to the right of the main
These columns were IF then statements such as
=IF(I3="r",+B3&C3,"") where
I is status
B3 is Volag
C3 is County
That one being in S

In the U column I put the year and month concatenated in
=+S3&+YEAR(A3)&+MONTH(A3)
Where A was the date of arrival

I suppose that could have been one command, but I had other sheets working
off the data of where.

In the Total Sheet I had
Column A Volag
Column B County
Row 2 Date by month

Finally the calculation for the total was
=+COUNTIF('Client List'!\$U:\$U,\$A4&\$B4&+YEAR(E\$2)&+MONTH(E\$2)).

And that totaled the number of people who came in on that date in that
county with that assigned VOLAG.

I'd post the sheet, but I don't think you do an attachment.