Forum Discussion
Solved
SUMIF Error when the range changes dynamically
I have a data set that generates weekly of raw data. I am trying to use the following formula to 'clean' and organize it so others can actually understand what is going on.
This is what I am starting with:
=SUMIFS(Candidate!$O:$O, MID(Candidate!$AU:$AU, 5, LEN(Candidate!$AU:$AU)), Hiring!$E$5)
This formula automatically gives me an error, I am assuming because of the SUMIF and that I am not defining a range. The problem is the range changes all the time. It could be until row 40, or until row 500. (generally does not go past 500).
Broken down what I am trying to do: Column AU on the Candidate tab has data that looks like, 407 namex. I am telling the formula to just give me namex, without the 407.
I want it to then count and give me the SUM of numbers for namex in column O.
With all of this I want it to match the name I have chosen in cell E5 on the Hiring tab. So if I choose nameY, I would get the total there.
And then further to match the state to the sum of each name.
Hiring tab:
Candidate Tab:
Please let me know if that does not make sense. Thank you!
- What am I doing wrong if I want to match it to the state? i.e. State1 would show 4 in the submitted category if I chose namex. I was trying to use a similar formula but receive an error.
=SUM(IF(ISNUMBER(MATCH(Hiring!$E$7:$E$325, Candidate!$AK$5:$AK$1000, 0)), IF(Candidate!$AU$5:$AU$1000=Hiring!$E$4, Candidate!$O$5:$O$1000, 0), 0))In a cell in row 9:
=SUMIFS(Candidate!$O$5:$O$1000, Candidate!$AU$5:$AU$1000, "*"&Hiring!$E$5, Candidate!$AK$5:$AK$1000, Hiring!$E9)
- How are you so smart. Thank you!!!!
