sum and if

[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

 

[Pete_UK]

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

 

[Dave]

Pete, thanks and also for explaining it so well!

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_UK]

You're welcome, Dave - thanks for feeding back.

Pete

 

[Ashish Mathur]

Hi,

Try this

=sumproduct((E4:E12="171570-1")*(G4:G12>=4)*(F4:F12))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com