I need to perform a SUMIF, SUMPRODUCT, or similar formula with TWO criteria, one a positive match and the other a negative match. Refer to the very shady table below:
A........| B.............| C.................| D..........| E......................| F...............................|G ID.......| NAME..........| HOUR..............| QTY........| UNIQUE LIST ...........| SELECTED HOUR...................|RESULT 4478....| BOB.......| 0000 - 0100 | 5...........| 4478...............| 2000 - 2100..............| ? 9922...| BILL.......| 0000 - 0100 | 4...........| 804.................| ..................................| 804....| FRED......| 0200 - 0300 | 2...........| 812.................| ..................................| 812....| GARY......| 0200 - 0300 | 9...........| ...................... |.................................. | 4478...| BOB.......| 1100 - 1200 | 7...........| ...................... | ..................................| 804....| FRED......| 1100 - 1300 | 4...........|....................... | ..................................| 9922...| BILL.......| 1800 - 1900 | 4|......... | ...................... | 9922...| BILL.......| 2000 - 2100 | 5|......... | ...................... | 812....| GARY......| 2200 - 2300 | 3| ........ | ...................... | 9922...| BILL.......| 2000 - 2100 | 6|......... | ...................... |
I Want to add the QTY's in column D together IF:
- The hour in column C DOES match the hour written in column F
- The staff number in column A IS NOT on the unique list in column E
The right formula would select rows 8 and 10 and add them together for a total of 11.
I can achieve the first with a sumif and the second with a sumproduct, but can't work out how to combine the two.
Thanks!