countif/sumproduct = criteria with weeknum
 

I need to create a count if cola and colb meet a certain criteria. Example:

A B
6-Jul dog
13-Jul dog
6-Jul cat
6-Jul dog
13-Jul dog
13-Jul dog
13-Jul cat
13-Jul dog

The count would be 2 as I am looking for dogs on July 6. The hiccup is the
date. I need it to calculate the WEEKNUM as a separate entity for each
weekending. The output should look something like this.

W/E count dog count cat
6-Jul 2 1
13-Jul 4 1

I currently have this:

=SUMPRODUCT((A2:A5001=39970)*(E2:E5001="dog"))

But I have to go in and generate a new formula for each weekending, and I
need to avoid that.

Many thanks for any help.

Regards

countif/sumproduct = criteria with weeknum
 

Use Autofilter to generate a unique list of dates

1. Select the range in Col A (date) including the header. You need to have
header. 2. From menu DataFilterAdvanced FilterCopy to another location
3. In copy to I have selected D1 and check 'Unique records only'
4. Click OK will give you the unique list of dates in Col D. (Format to any
date format if excel do not)
5. Put headers for each categories in row 1 from cell E1, F1 etc; ('dog',
'cat' etc ..)
6. In E2 apply the below formula. Make sure the headers are exactly same as
the entries in ColB of your data.
=SUMPRODUCT(--($A$1:$A$5000=D2),--($B$1:$B$5000=D$1))
7. Copy the formula to F2. Copy down as required

ColD ColE ColF
Dates dog cat
6-Jul 2 1
13-Jul 4 1



If this post helps click Yes
---------------
Jacob Skaria


"ummone" wrote:

I need to create a count if cola and colb meet a certain criteria. Example:

A B
6-Jul dog
13-Jul dog
6-Jul cat
6-Jul dog
13-Jul dog
13-Jul dog
13-Jul cat
13-Jul dog

The count would be 2 as I am looking for dogs on July 6. The hiccup is the
date. I need it to calculate the WEEKNUM as a separate entity for each
weekending. The output should look something like this.

W/E count dog count cat
6-Jul 2 1
13-Jul 4 1

I currently have this:

=SUMPRODUCT((A2:A5001=39970)*(E2:E5001="dog"))

But I have to go in and generate a new formula for each weekending, and I
need to avoid that.

Many thanks for any help.

Regards

countif/sumproduct = criteria with weeknum
 

You can use the DATE() function (second criteria in cell D1)
=SUMPRODUCT(--(A1:A5000=DATE(2009,7,6)),--(B1:B5000=D1))

With your second criteria in D1 and date in C1
=SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=D1))

in case of a date range it would be
=SUMPRODUCT(--(A1:A5000=C1),--(A1:A5000<=C2),--(B1:B5000=D1))


If this post helps click Yes
---------------
Jacob Skaria


"ummone" wrote:

I need to create a count if cola and colb meet a certain criteria. Example:

A B
6-Jul dog
13-Jul dog
6-Jul cat
6-Jul dog
13-Jul dog
13-Jul dog
13-Jul cat
13-Jul dog

The count would be 2 as I am looking for dogs on July 6. The hiccup is the
date. I need it to calculate the WEEKNUM as a separate entity for each
weekending. The output should look something like this.

W/E count dog count cat
6-Jul 2 1
13-Jul 4 1

I currently have this:

=SUMPRODUCT((A2:A5001=39970)*(E2:E5001="dog"))

But I have to go in and generate a new formula for each weekending, and I
need to avoid that.

Many thanks for any help.

Regards