conditional counting

[Guest]

Here is my data (in actuality, it's about 35,000 rows long and will be
growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled
as "Format 1".

TYPE (column A) PERIOD (column B) VERSION (column C)
DS 02/2005 1
BS 02/2005 2
WS 03/2005 2
DS 02/2005 1

I want to be able to count all of the different combinations that occur
(i.e., count all of the "DS and 02/2005 and 1") and return them in a sheet
I've labeled "Results"

There is too much data for a pivot table and I know there's a solution using
sumproduct (or something like that), but I haven't been able to figure it
out.

Any ideas?

Thanks,

Jim

 

[Bob Phillips]

Put DS in M1, the date in M2, version in M3 and use

=SUMPRODUCT(--(A2:A12000=M1),--(B2:B12000=M2),--(C2:C12000=M3))

 

[Guest]

Thanks Bob, that worked like a charm.

Now, let's say I have data in column D that I want to sum if the below
conditions are met. How would I write a formula to do that?

What I'm headed for here is an 'averageif' (which I have read is a
sumif/countif).

Thanks,

Jim

 

[Bob Phillips]

Just use Average itself

=AVERAGE(IF(A2:A12000&""=M1)*(B2:B12000=M2)*(C2:C12000=M3),D212000)

which is an array formula, so commit with Ctrl-Shift-Enter

 

[Guest]

Can you double-check that formula? I'm getting an error at the M1 part.

Thanks

 

[Bob Phillips]

Sorry, don't know what happened there

=AVERAGE(IF((A2:A12000&""=M1)*(B2:B12000=M2)*(C2:C12000=M3),D212000))

still array entered.