Nested IF/COUNTIF

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I've got two columns of data, column A is a date column,
and column B is a text column, with the text column being
one of 5 values. My goal is to count the quantity of
values from column B on a per date basis. I can do this
via a pivot table, but I can't figure out how to do this
via a function.

Example:

Column A Column B
11/16/2003 dog
11/16/2003 dog
11/16/2003 cat
11/17/2003 cat
11/17/2003 mouse
11/18/2003 dog
11/18/2003 mouse


I need an output that shows
dog cat mouse total
11/16/2003 2 1 0 3
11/17/2003 0 1 1 2
11/18/2003 1 0 1 2

I've tried several nested if's:

IF(columnA=11/16/2003,(countif(columnB,"cat")),"")

and the vice versa (doing the if on the text first).


Can this be done? I'm at a lost.
 
Let A1:B8 house the sample you provided with added labels:

{"Date","X";37941,"dog";37941,"dog";37941,"cat";37942,"cat";37942,"mouse";37
943,"dog";37943,"mouse"}

Create a unique date list in D from D2 on:

Date
11/16/2003
11/17/2003
11/18/2003


In E2:G2 enter the items of:

{"cat","dog","mouse"}

In E3 enter and copy across then down:

=SUMPRODUCT(--($A$2:$A$8=$D3),--($B$2:$B$8=E$2))

I leave the summing (the totals) to you.
 
Hi
try the following in your report range (lets say in cell B2 / cell
B1='cat' and cell A2 is one of your dates)
=SUMPRODUCT(('source_sheet'!$A$1:$A$1000=$A2)*('source_sheet'!$B$1:$B$1
000=B$1))
copy down and to the right

Assumption: your source data is on the separate sheet 'soruce_sheet')
 
Back
Top