| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 3 matching values of which one is a range and return a sum
A B C D E
1 Acc Code Acc Name Div Dept $ 2 60001 Com - Tel Rm 350 3 60002 Com - HP Rm 450 4 60003 Com - Fax Rm 650 5 60001 Com - Tel FB 750 6 60002 Com - HP FB 850 Report of Rm Div/Dept To show Com Cost 1450 Report of FB Div/Dept To show Com Cost 1600 Com Cost is made up of acc code from 60001 to 60003 ie I would like to set a formula to search for 60001 to 60003 in column A, Rm in column B and Rm in column C and then return the sum Please help and thank you in advance |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 3 matching values of which one is a range and return a sum
search for 60001 to 60003 in column A,
Rm in column B and Rm in column C Can't tell where one column ends and the next one begins: A B C D E 1 Acc Code Acc Name Div Dept $ 2 60001 Com - Tel Rm 350 I guess 60001 is in column A, Com - Tel is in column B? RM is in column C? If that's the layout there are no RM's in column B. ?????????? How about this, search for 60001 to 60003 in column A, Rm in column C, then sum the values in column D =SUMPRODUCT(--(A1:A5=60001),--(A1:A5<=60003),--(C1:C5="RM"),D1:D5) Better to use cells to hold the criteria: G1 = 60001 H1 = 60003 I1 = RM =SUMPRODUCT(--(A1:A5=G1),--(A1:A5<=H1),--(C1:C5=I1),D1:D5) If you're using Excel 2007: =SUMIFS(D1:D5,A1:A5,"="&G1,A1:A5,"<="&H1,C1:C5,I1 ) -- Biff Microsoft Excel MVP "Cheryl" wrote in message ... A B C D E 1 Acc Code Acc Name Div Dept $ 2 60001 Com - Tel Rm 350 3 60002 Com - HP Rm 450 4 60003 Com - Fax Rm 650 5 60001 Com - Tel FB 750 6 60002 Com - HP FB 850 Report of Rm Div/Dept To show Com Cost 1450 Report of FB Div/Dept To show Com Cost 1600 Com Cost is made up of acc code from 60001 to 60003 ie I would like to set a formula to search for 60001 to 60003 in column A, Rm in column B and Rm in column C and then return the sum Please help and thank you in advance |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup 3 matching values of which one is a range and return a sum
Hi,
Since Com accounts run from 60001 - 60003, this is duplicate information. So you could create the following formula: SUMPRODUCT(--(LEFT(B1:B5,3)="Com"),--(D1:D5="Rm"),E1:E5) If this hleps, please click the Yes button Cheers, Shane Devenshire "Cheryl" wrote: A B C D E 1 Acc Code Acc Name Div Dept $ 2 60001 Com - Tel Rm 350 3 60002 Com - HP Rm 450 4 60003 Com - Fax Rm 650 5 60001 Com - Tel FB 750 6 60002 Com - HP FB 850 Report of Rm Div/Dept To show Com Cost 1450 Report of FB Div/Dept To show Com Cost 1600 Com Cost is made up of acc code from 60001 to 60003 ie I would like to set a formula to search for 60001 to 60003 in column A, Rm in column B and Rm in column C and then return the sum Please help and thank you in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return values from an array based on matching text value | Excel Worksheet Functions | |||
| Return Numeric Values Matching EXACT Date for Criteria | Excel Worksheet Functions | |||
| Return all matching values | Excel Worksheet Functions | |||
| How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
| Matching unsorted lookup values | Excel Worksheet Functions | |||
Linear Mode
