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:G12VALUE("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:G123)*(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:G123)*(E4:E12="171570-1"),F4:F12)

Note the similarities.

Hope this helps.

Pete

On Sep 8, 10:29*pm, Dave wrote:
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:G12VALUE("3"),IF(E4:E12="171570-1",SUM(F4:F12),0),0))

What should the statement be?

Thanks

David

Pete, thanks and also for explaining it so well!

"Pete_UK" wrote:

Try it like this:

=SUM(IF((G4:G123)*(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:G123)*(E4:E12="171570-1"),F4:F12)

Note the similarities.

Hope this helps.

Pete

On Sep 8, 10:29 pm, Dave wrote:
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:G12VALUE("3"),IF(E4:E12="171570-1",SUM(F4:F12),0),0))

What should the statement be?

Thanks

David

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

Pete

On Sep 9, 12:25*am, Dave wrote:
Pete, thanks and also for explaining it so well!



"Pete_UK" wrote:
Try it like this:

=SUM(IF((G4:G123)*(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:G123)*(E4:E12="171570-1"),F4:F12)

Note the similarities.

Hope this helps.

Pete

On Sep 8, 10:29 pm, Dave wrote:
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:G12VALUE("3"),IF(E4:E12="171570-1",SUM(F4:F12),0),0))

What should the statement be?

Thanks

David- Hide quoted text -

- Show quoted text -

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dave" wrote in message
...
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:G12VALUE("3"),IF(E4:E12="171570-1",SUM(F4:F12),0),0))

What should the statement be?

Thanks

David