Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct and sumif | Excel Worksheet Functions | |||
SumIf or SumProduct | Excel Discussion (Misc queries) | |||
SUMIF vs SUMPRODUCT | Excel Worksheet Functions | |||
HELP !!! SUMIF or SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumif and sumproduct together | Excel Discussion (Misc queries) |