I'm using the SUMIFS function to assemble unique values into a summary table in a different layout than the source table. The source is four columns with 171 rows and the destination is 10 columns with 60 rows. Setting conditions on three of the source columns (A, B and D) determines a unique value for the fourth (the value being exported), but when that condition doesn't exist, I get a zero in the target cell. Since I will be graphing the results, I want to be able to differentiate between a true zero (of which there are only a couple), and a zero that is just a place-holder in the table; I would like my graph lines to have gaps where there are no values. Note that the target cells are not entered as array formulas, but I get correct values, except for the filler zeroes.
I tried a nested IF(AND() with the three conditions in the AND and an array as the iftrue result, but got all FALSE values. I also tried error trapping with an if false=>"" type of command before my sum, but failed there, too.
I'm attaching a version of the database that shows the problem.
AYSample.xlsx
Bob
Bookmarks