Counting with multiple criteria

[scaffdog845]

Morning all,

I have common tasks where I need to sort through large amounts of data to receive a total number of line entries matching multiple criteria in different columns. I've attached a Workbook with a simplified example. On STATS!B1 I would like to total, from DATA!, all lines which have "1A" in A:A and any of the following in E:E (RP, SAO, AE, RSNR).

As always, help from the group is appreciated.

[NBVC]

Try:

=SUMPRODUCT((DATA!A:A="1A")*(DATA!E:E={"RP","SAO","AE","RSNR"}))

although faster if you used defined ranges... and if you have just "1A" in A1 so that formula can reference the cell instead of hard-coding "1A" into it.

[scaffdog845]

I see what you mean. The formula worked relatively well in the small example file, however when I tried it in the working workbook (containing 2632 records) and added the additional criteria (8 more match types) Excel ran out of available resources. I broke it down into chunks and added them together which seemed to help, but it still took a few seconds. The final formula looked like this =SUMPRODUCT(('Planning Data'!A:A="1A")*('Planning Data'!E:E={"PAA","RS"}))+SUMPRODUCT(('Planning Data'!A:A="1A")*('Planning Data'!E:E={"RSNR","S"}))+SUMPRODUCT(('Planning Data'!A:A="1A")*('Planning Data'!E:E={"SAM","SAMT"}))+SUMPRODUCT(('Planning Data'!A:A="1A")*('Planning Data'!E:E={"SAO","SAT"}))+SUMPRODUCT(('Planning Data'!A:A="1A")*('Planning Data'!E:E={"SOR","WKS"}))

My profile may be old and still reflecting MS Excel 2003. I have since upgraded to MS Excel 2007 if that helps with any new formulae.

[NBVC]

Does converting the range to a defined range improve it?

e.g=SUMPRODUCT((DATA!A$1:A$2600="1A")*(DATA!E$1:E$2600={"RP","SAO","AE","RSNR"}))

[scaffdog845]

Works great with a defined range. I wasn't even thinking about the millions of cells the formula had to parse in '07. I cut it down to 3 times the expected range to be safe and bingo.

THANKS!