Link to home
Create AccountLog in
Avatar of Ladkisson
LadkissonFlag for United States of America

asked on

Broken sumifs formula

My current sumifs formula is linked to the drop down box and  is pulling wrong data. Examples are highlighted - for a needed week I get an incorrect mapping. B9 sheet A has week 41, but C28&C29 sheet A is pulling from the right tab but from a wrong week.
 Please refer to the attached file and let me know how it needs to be modified/fixed. Thanks!!
Sumifs.xlsx
Avatar of NBVC
NBVC
Flag of Canada image

You neglected to make the B range in the formula Absolute so that when you copy down the range doesn't change.

I.e.

In F20:

=SUMIFS('C'!$C$3:$C$17,'C'!$A$3:$A$17,A!B20,'C'!$B$3:$B$17,A!B$9)

and in G20:

=SUMIFS(B!$D$2:$D$166,B!$A$2:$A$166,A!B20,B!$B$2:$B$166,A!B$9)
ASKER CERTIFIED SOLUTION
Avatar of jsdray
jsdray
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ladkisson

ASKER

Great Help! Thank you!
I don't think the criteria 2 range was wrong... it was just not made absolute!!
Your are right! The formula works perfectly! Thanks again!
You might want to talk to a mod then about adjusting the Accepted Solution to the correct one.... for future solution seekers.