Sumif or Sumproduct?
 

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!!!
--
-A

Sumif or Sumproduct?
 

Hi,

Here is one way

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"alarson01" wrote:

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!!!
--
-A

Sumif or Sumproduct?
 

I tried it and now I get a #VALUE! error.
Let's complicate the issue a little more....

The description in either column can include part of the word or all (AMEX
or AM).
Also, I have two $ amount columns; one for debits, one for credits...

Help? :)
--
-A


"Shane Devenshire" wrote:

Hi,

Here is one way

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"alarson01" wrote:

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!!!
--
-A

Sumif or Sumproduct?
 

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"),D2:D7)

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")) *D2:D7)-(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"))*D2:D7)-(A2:A7=1)*((LEFT(B2:B7,2)="AM")+(LEFT(C2:C7,2)="AM "))*E2:E7)

Best wishes
Dave

"alarson01" wrote in message
...
I tried it and now I get a #VALUE! error.
Let's complicate the issue a little more....

The description in either column can include part of the word or all (AMEX
or AM).
Also, I have two $ amount columns; one for debits, one for credits...

Help? :)
--
-A


"Shane Devenshire" wrote:

Hi,

Here is one way

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"alarson01" wrote:

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!!!
--
-A