LookUP formula using 3 conditions

[Ecel Dumbo]

Hello Dear Friends,


Could you please refer the attached spreadhseet below and kindly suggest a formula to lookup sales values using 3 conditions.


http://speedy.sh/eT9xa/LookUP-Formula-with-mutiple-conditions.xls


Thanks heaps

Excel Dumbo

 

[vasim]

C9 = =COUNTIFS('LookUP Sheet'!$E:$E,Result!$A$7,'LookUP Sheet'!$F:$F,Result!$A9,'LookUP Sheet'!$G:$G,Result!C$8)


you might want to change the column range as required....

Drag it across and below as required...

 

[Ecel Dumbo]

HI Indian...Thanks for your reply. I applied your formula and it came up with #NAME error. Please advise

 

[vasim]

Missed out...you want sales figure, try this


C9

=SUMIFS('LookUP Sheet'!$K$1:$K$500,'LookUP Sheet'!$E$1:$E$500,Result!$A$7,'LookUP Sheet'!$F$1:$F$500,Result!$A9,'LookUP Sheet'!$G$1:$G$500,Result!C$8)


When changing range, change all of them and press enter

 

[Kevin@Radstock]

Hi Ecel Dumbo


Try the following LOOKUP,in C3 copy across and down. The TRIM fuction has been used as you have trailing spaces.


=LOOKUP(2,1/(('LookUP Sheet'!$E$2:$E$61=$A$1)*('LookUP Sheet'!$G$2:$G$61=C$2)*('LookUP Sheet'!$F$2:$F$61=TRIM($A3))),'LookUP Sheet'!$K$2:$K$61)


OR a SUMPRODUCT:

=SUMPRODUCT(--('LookUP Sheet'!$E$2:$E$61=$A$1),--('LookUP Sheet'!$F$2:$F$61=TRIM($A3)),--('LookUP Sheet'!$G$2:$G$61=C$2),'LookUP Sheet'!$K$2:$K$61)


@ Indian. SUMIFS is not available in Excel 2003<

 

[vasim]

Ohhhhh There you go....I was still wondering why the **** error.....

 

[Ecel Dumbo]

THanks Indian and Kevin. Appreciate your help.