Forum Discussion
Sum of data across multiple columns & rows based on Criteria
The problem is that the ranges aren't the same shape and don't even line up in some cases. That said if you want conditionals on both horizontal and vertical I would suggest you could try a more direct product for your conditionals. Traditionally we would use SUMPRODUCT but probably don't really need that now, but maybe something like:
=SUM('Query Budget'!$C$10:$HV$1000 * ('Query Budget'!$A$10:$A$1000=F$1)*('Query Budget'!$C$5:$HV$5=$D5))
Unfortunately, that didn't work. Thank you, though.
"that didn't work"? did you get an error or just not get the answer you wanted? considering you didn't give us much to go on but a formula that "doesn't work" I really wasn't necessarily expecting (but hoping) the exact formula I gave you would give you the exact answer you wanted but that the concept and the format was a way for you to do what I think you want to do.
That said, I do notice you have absolute references for nearly all except for the F$1 and $D5, are you expecting them to be relative / change with each value in some way? If so that won't happen/work and you have to specify the actual array to use (and the dimensions must match.
If you can specify what didn't work and maybe more details on what you are trying to do then maybe I can get you to the finish line.