sum and if

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have data in 3 columns. I want to sum the quantity data if 2 criteria are
met i.e. if the status is 4 or greater and if the part number matches.

Part Number (Col E) Quantity (Col F) Status
1715-1 1 4
...... .....
.....

I put a simple nested if statement together but it gives me a value error.
=SUM(IF(G4:G12>VALUE("3"),IF(E4:E12="171570-1",SUM(F4:F12),0),0))

What should the statement be?

Thanks

David
 
Try it like this:

=SUM(IF((G4:G12>3)*(E4:E12="171570-1"),F4:F12))

This is an array formula, which needs to be committed using the key
combination of Ctrl-Shift-Enter (CSE) rather than the usual <enter>.
If you do this correctly then Excel will wrap curly braces { } around
the formula when viewed in the formula bar - do not type these
yourself. If you need to edit the formula, you need to use CSE again.

An alternative (non-array entered) formula is:

=SUMPRODUCT((G4:G12>3)*(E4:E12="171570-1"),F4:F12)

Note the similarities.

Hope this helps.

Pete
 
Pete, thanks and also for explaining it so well!

Pete_UK said:
Try it like this:

=SUM(IF((G4:G12>3)*(E4:E12="171570-1"),F4:F12))

This is an array formula, which needs to be committed using the key
combination of Ctrl-Shift-Enter (CSE) rather than the usual <enter>.
If you do this correctly then Excel will wrap curly braces { } around
the formula when viewed in the formula bar - do not type these
yourself. If you need to edit the formula, you need to use CSE again.

An alternative (non-array entered) formula is:

=SUMPRODUCT((G4:G12>3)*(E4:E12="171570-1"),F4:F12)

Note the similarities.

Hope this helps.

Pete
Click to expand...
 
Back
Top