ExcelBanter - sumproduct of two categories

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - sumproduct of two categories (https://www.excelbanter.com/excel-worksheet-functions/111740-sumproduct-two-categories.html)

sumproduct of two categories

I have a sumproduct to count items within a given date range:

=SUMPRODUCT(--(((TDL!P2:P700<=AE4)*TDL!P2:P700)=AC4))

but I need narrow this even further to count only items that match "Gadget"
in another range of entries.

.....and (S&D!H2:H700="Gadget")

I keep getting errors (#N/A).

Ron Coderre

sumproduct of two categories

Try this:

=SUMPRODUCT((TDL!P2:P700=AC4)*(TDL!P2:P700<=AE4)* (S&D!H2:H700="Gadget"))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

"CJ" wrote:

I have a sumproduct to count items within a given date range:

=SUMPRODUCT(--(((TDL!P2:P700<=AE4)*TDL!P2:P700)=AC4))

but I need narrow this even further to count only items that match "Gadget"
in another range of entries.

....and (S&D!H2:H700="Gadget")

I keep getting errors (#N/A).

All times are GMT +1. The time now is 11:59 AM.