0

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:

  1. The hour in column C DOES match the hour written in column F
  2. 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!

  • What are the two formulas? – Scott Craner May 16 '16 at 13:36
  • I have tried fixing your formatting as best I can, please take a look a look and check That everything lines up as it should. Take a look at Formatting Help – Burgi May 24 '16 at 7:55
0

The formula you want is:

=SUMPRODUCT((F2=$C$2:$C$11)*(COUNTIF($E$2:$E$4,$A$2:$A$11)=0)*(D2:D11))

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.