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!

2
  • What are the two formulas? May 16, 2016 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, 2016 at 7:55

1 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .