SUMIF with varying number of rows

[Pirlo81]

Hello

I am trying to do a SUMIF function in one sheet that reads the info from another sheet. The problem is that the data on the sheet I am running the SUMIF on is copied and pasted so the number of rows varies every time. How can I accommodate this in the SUMIF? I tried to account for this by making the range of rows large to accommodate whatever number of rows that are pasted but the problem is that if the rows are empty, the formula returns an error. How can I make the SUMIF function ignore the blank rows?

 

[KRice]

You could construct a dynamic array that will determine the lower extent of the range and adjust the range accordingly. Alternatively, you could convert the data to be summed to an official Excel table and use structured references that will operate on the entire column referenced. Do you have a small sample showing what you want to do? What version of Excel are you using?

 

[KRice]

Alternatively, if you have Excel 365, you could use the FILTER function to eliminate any empty cells in an overly large range.