SUMIF using Multiple Comparisons

**DCSwearingen** · 03-24-2005, 04:22 PM

I have a spreadsheet ("H20") formatted as follows:
Col A Col B Col C Col D Col E Etc
Date/Time Shift Meter A Meter B Meter C
3/2/04 12:17 AM G 2,776 26,613 3,204
3/2/04 1:18 AM G 2,264 26,406 3,909
3/2/04 3:18 AM G 3,357 26,485 2,408
3/2/04 4:18 AM G 2,941 27,105 2,428
3/2/04 5:18 AM G 3,060 27,514 4,571
3/2/04 6:19 AM G 2,970 27,524 2,979
3/2/04 7:19 AM G 236 28,803 3,219
3/2/04 8:19 AM D 57 26,488 4,172
3/2/04 9:19 AM D 154 25,195 3,690
3/2/04 10:20 AM D 31 25,823 4,734
3/2/04 11:20 AM D 59 25,272 5,637
3/2/04 12:20 PM D 39 25,137 3,423
3/2/04 1:20 PM D 91 25,330 4,719
3/2/04 2:20 PM D 150 25,549 3,754
3/2/04 3:20 PM D 33 26,654 3,819
3/2/04 4:21 PM S 63 27,532 3,566
3/2/04 5:21 PM S 83 27,390 5,281
3/2/04 6:21 PM S 23 27,614 2,874
3/2/04 7:21 PM S 86 27,021 2,870
3/2/04 8:21 PM S 25 27,295 3,196
3/2/04 9:22 PM S 221 27,226 2,872
3/2/04 10:22 PM S 43 27,548 2,903
3/2/04 11:22 PM S 1,748 26,364 3,465
3/3/04 12:23 AM G 2,832 27,842 2,960
3/3/04 1:23 AM G 2,285 27,817 3,700

This continues for virtually every hour for over a year.

I have a second spreadsheet ("Shifts") formatted as follows:
Col A Col B Col C Col D Col E Etc
Date/Time Shift Meter A Meter B Meter C
3/2/04 12:00 AM G
3/2/04 8:00 AM D
3/2/04 4:00 PM S
3/3/04 12:00 AM G
3/3/04 8:00 AM D
3/3/04 4:00 PM S
3/4/04 12:00 AM G
3/4/04 8:00 AM D
3/4/04 4:00 PM S
3/5/04 12:00 AM G

On this second sheet I want to use a SUMIF function that will
* sum H20!C$2:C$9000

* if INT(H20!A$2:A$9000) = INT(Shifts!A2)
AND
* if H20!B$2:B$9000 = Shifts!B2

This formula would go into Shifts!C2 and be copied down, with a similar formula entered into Shifts!D2 etc.

The result of the formula in Shifts!C2 should be 20,207
The result of the formula in Shifts!C3 should be 615

I have tried pivot tables and sub-totals. Pivot tables only handle 8,000 rows, and sub-totals take 30-50 minutes to process

, and do not have a date associated with the shift when compressed to the shift level.

I have tried every variation I can think of for the SUMIF function with either everything being zero or the whole column totaled every time. I would like to be pointed in the right direction.

**Domenic** · 03-24-2005, 07:14 PM

Try...

C2, copied down and across:

=SUMPRODUCT(--(INT(H2O!$A$2:$A$9000)=INT($A2)),--(H2O!$B$2:$B$9000=$B2),H2O!C$2:C$9000)

Hope this helps!

**DCSwearingen** · 03-25-2005, 05:36 PM

This did the job for me!!

I guess I need to look into SUMPRODUCT and see how it works. I would never have thought about this function the way it is explained in Excel Help...

As for the syntax, what do the '--' mean in the formula? I tried removing them and it returned all zeros.