SumProduct Formula not working

**jfwidt** · 02-03-2010, 12:54 PM

Hello,

I'm trying to populate a field if a certain date range has a specific quality, e.g. between Jan 01 and Jan 31, cars sold that are red, provide the already summed total of red cars.

=SUMPRODUCT((DateRec>=DATE(2009,1,1)*(DateRec<=DATE(2009,1,31)*(P3:S3=AH4)*V4)))

It doesn't seem to be pulling the data.

Thanks

**NBVC** · 02-03-2010, 01:04 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**jfwidt** · 02-03-2010, 01:16 PM

Thanks for the reply. It's been educational reading through all the post here.

Please see an attached example of what I'm trying to do. Thanks

**NBVC** · 02-03-2010, 02:04 PM

If you remove the Received part from I6:I8 (including the space after Cars), then it makes it easier...

=SUMPRODUCT((TEXT($B$6:$B$10,"mmm")=J$5)*($C$5:$E$5=$I6),$C$6:$E$10)

copied down and to the next column....

**jfwidt** · 02-03-2010, 04:57 PM

Thank you for the help, that worked! In going a step further, I have a spreadsheet similar to the one I've attached. Is there a way to gather the information as shown in the bottom half? I've tried, but with no success.
This one might be more difficult
Thanks!

**NBVC** · 02-03-2010, 05:04 PM

Try in E21:

=SUMPRODUCT((TEXT($D$5:$D$11,"mmm")=E$20)*($E$4:$H$10=$D21),($E$5:$H$11))

Then copied across the matrix.

Notice the offset in ranges in the 3 conditions...