Sumif or Sumproduct?

[alarson01]

I simply can't figure this out...

I have multiple locations that I need to find specific criteria and sum:

A B C
D
LOCATION DESCRIPTION1 DESCRIPTION2 AMOUNT
------------- ----------------- ----------------- ------------
1 MC blank
$50
1 PURCHASE MC $20
1 AMEX blank
$10
5 AMEX blank $25
7 PURCHASE VISA $75
7 VISA blank
$100

For example:
I want to find to total $ for location 1 with description of MC in either
column B or C. So this example would total $70.
Help?
Thank you!!!

 

[Shane Devenshire]

Hi,

Here is one way

=SUMPRODUCT((A1:A6=1)*(B1:C6="MC")*D16)

 

[Bassman62]

Hi,

Here is an excelent source of information on SUMPRODUCT
http://xldynamic.com/source/xld.SUMPRODUCT.html
I am but a pauper in a sea of experts here and it took me a while to gain an
understanding of this function but eventually some of the concepts and
applications started to click. I would recommend investing some time at the
site above. One thing that helped me was to build the formula using the
"Insert Fuction" feature (click on the "fx" on the formula bar). You can see
the immediate results as you build each array.
If Debit is column D and Credit is column E:
This will return the amount of debits where Location = 1 and "MC" is found
in column B or C
=SUMPRODUCT(--(A2:A7=1),(B2:B7="MC")+(C2:C7="MC"),D27)

This will return the amount of debits less credits where Location = 1 and
"MC" is found in column B or C.
=SUMPRODUCT(((A2:A7=1)*((B2:B7="MC")+(C2:C7="MC"))*D27)-(A2:A7=1)*((B2:B7="MC")+(C2:C7="MC"))*E2:E7)

This will return the amount of debits less credits where Location = 1 and
"AM" are the first two characters in column B or C.
=SUMPRODUCT(((A2:A7=1)*((LEFT(B2:B7,2)="AM")+(LEFT(C2:C7,2)="AM"))*D27)-(A2:A7=1)*((LEFT(B2:B7,2)="AM")+(LEFT(C2:C7,2)="AM"))*E2:E7)

Best wishes
Dave