Sumproduct Function Using 4 Different Criteria

**LisaG** · 09-17-2008, 05:51 AM

Dear Forum

Please can you provide help with the following problem.

In the Summary Tab of the attached example spreadsheet, I need to calculate the total (for each day using the date field in column A) of Printers (column G) with the name of 'UKT7' with a German time of less than '16:00:00' (in column H) that have a reason code of 'Blank' in (coloumn I).

I have the following forumula which works great, but it doesn't include the 'less than 16:00:00' rule in column H. My current formula is returning a result of 13 for day '11.09.08' & this total should actually be 6 for that day as I need it to look at less than 16:00:00

=SUMPRODUCT(('11.09.08'!$A$1:$A$1000="11.09.08")*('11.09.08'!$G$1:$G$1000="UKT7")*('11.09.08'!$I$1:$I$1000=""))

Can anyone help?

I would be extremely grateful.

Thanks

**dominicb** · 09-17-2008, 06:15 AM

Hi LisaG

Originally Posted by LisaG

In the Summary Tab of the attached example spreadsheet, I need to calculate the total (for each day using the date field in column A) of Printers (column G) with the name of 'UKT7' with a German time of less than '16:00:00' (in column H) that have a reason code of 'Blank' in (coloumn I).

Think this ticks all the right boxes - does it work OK for you?
=SUMPRODUCT(('11.09.08'!$A$1:$A$1000="11.09.08")*('11.09.08'!$G$1:$G$1000="UKT7")*('11.09.08'!$H$1:$H$1000<TIMEVALUE("16:00:00"))*('11.09.08'!$I$1:$I$1000=""))

HTH

DominicB

**LisaG** · 09-17-2008, 07:56 AM

Dear DominicB

Your suggestion works perfectly. Thank you so much for your time & effort helping me with this problem.

Your help is very much appreciated.

Thanks again.

Lisa