sumifs with horizontal ranges not working

Rob Wright

Hi everyone,

I am creating a metric sheet, and in it I am using sumifs to return figures for a financial forecast. In the first row, I only need to look up one month (which is it's own column), so the formula is =SUMIFS({apr forecast}, {project delivery status}, OR(@cell <> "Abandoned", @cell = ""), {financial year}, [Column4]33) where "apr forecast is just looking at the april column, the next bits are my filtering, and where [column44]3 is a helper cell i am using so i can change the formulas for the whole table at once when a filtering variable changes. The formula seems to work

When I go down to the next line, it s the same except that instead of {april forecast}, the range is {april may forecast} so it is looking at two columns to return totals from. This doesn't work and says incorrect argument set.

Does anyone have any ideas? It's worth noting that a simple sum formula was letting me return the ranges without filtering, but now that is sumifs it doesn't seem to want to. My first though is that because they are horizontal ranges I am doing something wrong?

Paul Newcome

It is because your ranges do not match in size. If one range is an entire column, then all ranges must be entire columns. If one range is 5 columns by 1 row, then all ranges must be 5 columns by 1 row.

To get around this, try creating two SUMIFS (one for April column and one for May column) and add them together.