A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sumif or Sumproduct?



 
 
Thread Tools Display Modes
  #1  
Old June 12th, 2009, 06:40 PM posted to microsoft.public.excel.worksheet.functions
alarson01
external usenet poster
 
Posts: 5
Default 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
  #2  
Old June 12th, 2009, 06:51 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_3_]
external usenet poster
 
Posts: 3,333
Default Sumif or Sumproduct?

Hi,

Here is one way

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

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

  #3  
Old June 12th, 2009, 07:09 PM posted to microsoft.public.excel.worksheet.functions
alarson01
external usenet poster
 
Posts: 5
Default 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")*D16)

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

  #4  
Old June 12th, 2009, 11:07 PM posted to microsoft.public.excel.worksheet.functions
Bassman62
external usenet poster
 
Posts: 75
Default 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"),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

"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")*D16)

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.