SUMIF formula with multiple conditions

[Guest]

I am looking for a way to shorten my current sumif formulas. I have two columns, A contains product codes, B contains numeric values, i.e. sales figures. Currently, if I am to add the sales figures for product codes 101, 102, 103, etc.,. I have to use:

=sumif(A1:A1000,"=101",B1:B1000)+sumif(A1:A1000,"=102",B1:B1000)+sumif(A1:A1000,"=103",B1:B1000)+sumif(...)...

As you can see this can get very cumbersome if I am looking to add up multiple products in the same column.

I have tried named range, i.e. Product ={"=101","=102","=103"} and insert name into formula =sumif(A1:A1000,Product,B1:B1000) and Ctrl+Shift+Enter to activate the array function...but nothing...the formula just picks up the first item 101 in the named range.

Another approach, I thought, was to use the SUMPRODUCT formula, and so went:
=SUMPRODUCT(--(A1:A1000="=101"),--(A1:A1000="=102"),--(A1:A1000="=103"),B1:B1000)...still nada!

This has been driving me bonkers for the last few days, I have tried a bunch of variations of the SUMIF and SUMPRODUCT formulas and can't think of anything else to try.

As you would've guessed, ANY help in the right direction is MUCH appreciated.

Regards,

VT

 

[Andy B]

Hi

You could try something like:
=SUMPRODUCT((A1:A1000={"101,"102","103"})*(B1:B1000))

--
Andy.

I am looking for a way to shorten my current sumif formulas. I have two

columns, A contains product codes, B contains numeric values, i.e. sales
figures. Currently, if I am to add the sales figures for product codes 101,
102, 103, etc.,. I have to use:

=sumif(A1:A1000,"=101",B1:B1000)+sumif(A1:A1000,"=102",B1:B1000)+sumif(A1:A1
000,"=103",B1:B1000)+sumif(...)...

As you can see this can get very cumbersome if I am looking to add up

multiple products in the same column.

I have tried named range, i.e. Product ={"=101","=102","=103"} and insert

name into formula =sumif(A1:A1000,Product,B1:B1000) and Ctrl+Shift+Enter to
activate the array function...but nothing...the formula just picks up the
first item 101 in the named range.

Another approach, I thought, was to use the SUMPRODUCT formula, and so went:

=SUMPRODUCT(--(A1:A1000="=101"),--(A1:A1000="=102"),--(A1:A1000="=103"),B1:B
1000)...still nada!

This has been driving me bonkers for the last few days, I have tried a

bunch of variations of the SUMIF and SUMPRODUCT formulas and can't think of
anything else to try.

 

[Andy Wiggins]

How about something like:
=SUM(SUMIF(A1:A1000,{101,102,103},B1:B1000))

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

I am looking for a way to shorten my current sumif formulas. I have two

columns, A contains product codes, B contains numeric values, i.e. sales
figures. Currently, if I am to add the sales figures for product codes 101,
102, 103, etc.,. I have to use:

=sumif(A1:A1000,"=101",B1:B1000)+sumif(A1:A1000,"=102",B1:B1000)+sumif(A1:A1
000,"=103",B1:B1000)+sumif(...)...

As you can see this can get very cumbersome if I am looking to add up

multiple products in the same column.

I have tried named range, i.e. Product ={"=101","=102","=103"} and insert

name into formula =sumif(A1:A1000,Product,B1:B1000) and Ctrl+Shift+Enter to
activate the array function...but nothing...the formula just picks up the
first item 101 in the named range.

Another approach, I thought, was to use the SUMPRODUCT formula, and so went:

=SUMPRODUCT(--(A1:A1000="=101"),--(A1:A1000="=102"),--(A1:A1000="=103"),B1:B
1000)...still nada!

This has been driving me bonkers for the last few days, I have tried a

bunch of variations of the SUMIF and SUMPRODUCT formulas and can't think of
anything else to try.

 

[vtisix]

Thanks for the effort. I tried it but got a #VALUE error.

 

[Andy B]

Hold on! I've missed out a " Try this:
=SUMPRODUCT((A1:A1000={"101","102","103"})*(B1:B1000))

 

[vtisix]

Awesome!!! That even works with named ranges. Thanks a bunch Andy.

 

[vtisix]

That didn't work either, #VALUE error. But the other Andy gave me the
solution that works. I appreciate your effort anyway.

 

[Guest]

Hi,

In cell C10 type, the array enter (Ctrl+Shift+Enter) the following formula:

SUM(IF((A1:A6=101)+(A1:A6=102)+(A1:A6=103),B1:B6))

Regards,

Ashish Mathur