View Single Post
  #5   Report Post  
Old April 21st 09, 01:44 AM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_] Fred Smith[_4_] is offline
external usenet poster
First recorded activity by ExcelBanter: Jan 2008
Posts: 2,389
Default 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.
works just as well, and will be easier for others to understand.


"Altair1972m" wrote in message

"Teethless mama" wrote:


"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

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

And what I was doing was counting all clients arriving during a month from
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
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.