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

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

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