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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|