Help with formula -- sumifs?

K

kelsey

New member
Joined
Mar 21, 2017
Messages
4
Reaction score
0
Points
0
View attachment example excel book.xlsx

I am trying to use a drop-down cell to pull from multiple criteria. I tried to color-code the cells to help explain.

I want to be able to read information on the main sheet from the # stores sheet. I'm not sure if the list sheet will be involved in anything other than the data validation for the yellow location drop-down box.

I feel like this is a sumifs formula, but I can't figure out how to make it work! Please help! :Cry:
 
Welcome to the forum!

It's not really clear what you are trying to do as you have not given us any expected outcomes. What would they be for the item currently chosen in the drop-down?
 
I think I understand, try this formula:

=IF($C$3="location","",SUMIFS(INDEX('# stores'!$A$1:$K$17,0,MATCH($C$3,'# stores'!$A$1:$K$1,0)+1),INDEX('# stores'!$A$1:$K$17,0,MATCH($C$3,'# stores'!$A$1:$K$1,0)),$B9))

copied down.
 
NBVC said:
I think I understand, try this formula:

=IF($C$3="location","",SUMIFS(INDEX('# stores'!$A$1:$K$17,0,MATCH($C$3,'# stores'!$A$1:$K$1,0)+1),INDEX('# stores'!$A$1:$K$17,0,MATCH($C$3,'# stores'!$A$1:$K$1,0)),$B9))

copied down.
Click to expand...

THIS is exactly what I needed! Thank you! :dance:
 
NBVC said:
I think I understand, try this formula:

=IF($C$3="location","",SUMIFS(INDEX('# stores'!$A$1:$K$17,0,MATCH($C$3,'# stores'!$A$1:$K$1,0)+1),INDEX('# stores'!$A$1:$K$17,0,MATCH($C$3,'# stores'!$A$1:$K$1,0)),$B9))

copied down.
Click to expand...

View attachment example workbook.xlsx

Hi NBVC, I've been trying to work with your example formula in my actual workbook, and for some reason it isn't working... The new attached workbook is using elements from my actual workbook.

Could you please help me to put the numbers from PEN, by location and item #, onto the PEN by LOCATION column in JCGUIDE#1, using the LOCATION drop-down menu?
 
You have one small error in the first MATCH() formula... it should reference only row 1.

=IF(NAME="LOCATION","",SUMIFS(INDEX(PEN!$A$1:$AD$199,0,MATCH(NAME,PEN!$A$1:$AD$1,0)+1),INDEX(PEN!$A$1:$AD$199,0,MATCH(NAME,PEN!$A$1:$AD$1,0)),$B7))
 
You're awesome! That worked much much better! :)
 
You must log in or register to reply here.
Back
Top