SUMIF formula using a drop down list

April 12, 2013 at 07:37:18
Specs: Windows 7
Please could someone help me. I have created a SUMIF formula using data from 3 sheets onto one master sheet. However, the cell that i have used as one of the criteria is from a drop down list. It only seems to work when the drop down list is on the same name when i created the formula.

So far this is my formula...
SUMIFS(ND!$V$3:$V$4000,ND!$F$3:$F$4000,"$A$1",ND!$B$3:$B$4000,TEST!A3)+SUMIFS(GK!$V$3:$V$4000,GK!$F$3:$F$4000,TEST!$A$1,GK!$B$3:$B$4000,TEST!A3)+SUMIFS(TW!$V$3:$V$4000,TW!$F$3:$F$4000,TEST!$A$1,TW!$B$3:$B$4000,TEST!A3)


$A$1 is the cell containing the drop down list.

Help much appreciated ASAP PLEASE


See More: SUMIF formula using a drop down list

Report •


#1
April 12, 2013 at 07:57:24
From just a quick glance at your formula, your first instance of $A$1
is surrounded by quotes.

SUMIFS(ND!$V$3:$V$4000,ND!$F$3:$F$4000,"$A$1",ND!$B$3:$B$4000,TEST!A3)

So unless you are looking for the TEXT string "$A$1"
you might want to remove the quotes.

And Excel questions are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/


Report •

#2
April 12, 2013 at 08:01:27
I've removed them which is still resulting the same problem. From browsing the internet, I am not too sure in an INDIRECT function would make it work but I am totally unsure where i would place this or even if it would work.

Report •

#3
April 12, 2013 at 08:33:17
If your using a Drop down list,
and nothing has been selected,
then A1 will have no data for the =SUMIFS to use.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 12, 2013 at 08:38:11
Sorry, i realise i was not clear. Only one selection from my drop down list actually works? Will keep trying solutions but thank you for your help!!

Report •

#5
April 12, 2013 at 08:58:28
Also notice that you have TEST!$A$1, which anchors cell A1
but that TEST!A3 is not anchored.

If you are dragging the formula, this will make a difference.

You also realize, that both parts of your =SUMIFS() must return TRUE
for it to actually sum.

So:

ND!$F$3:$F$4000,$A$1 must match
and
ND!$B$3:$B$4000,TEST!A3 must match

MIKE

http://www.skeptic.com/


Report •

Ask Question