View Single Post
  #3   Report Post  
Old April 20th 09, 08:49 PM posted to microsoft.public.excel.worksheet.functions
Altair1972m Altair1972m is offline
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 10
Default Count If Column A = x and Column B = y



"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 spreadsheet.
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.