I got a database that tracks 100 different items for 17 different cities plus a general one that sums or averages the results of all 17 cities within that item. Currently I am using a SUMIFS formula to search the array and give me the desired value.
My issue is, since SUMIFS returns a 0 if the item hasn’t been filled in yet, it ends up giving me alerts that the item value is lower than a desired threshold. I’m struggling to find a way that dynamically checks whether that cell is actually empty or a true 0 (as some items can be 0 for that month). Alternatively, a way to search the array and return me the value in the target cell even if it’s an empty cell would work too. I’m unsure of the best way to show what I got right now, so images it is.
This first image shows the visual side of the database that displays to users the current status of tracked items. Ideally, there would be no “0” in a place where there cell is actually empty.
This image contains a visual tracker of items
The second image shows how the database is currently organized.
database
I’m currently using the formula
=IF(ISEMPTY(DATA!H37);””;SUMIFS(DATA!H:H;DATA!$A:$A;’All Cities’!$B$2;DATA!$D:$D;’All Cities’!$D$6;DATA!$E:$E;’All Cities’!$L$8))
It works, but only under ideal conditions. If I apply filters to the database, or alter the order in which it is organized, the reference in “ISEMPTY” won’t check the correct cell anymore.
I’m struggling to make the ISEMPTY check dynamic the same way the SUMIFS one is, and haven’t been able to make good use of a index match combo to do this function, as I’m struggling to make it work with the multiple parameters involved. Currently stuck on excel 2010 as compatibility with older versions is needed.
Taric.png is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.