Formula to Use

Jeremy Horton

Hi,

I'm trying to figure out how to write a formula that would add the numbers in the "QTY" column that also match the selected "Replace in Fiscal Year" dropdown selection. The top rows 24-29 is where the totals would populate based on the matching of the "Replace in Fiscal Year" selected and "QTY" sum in the corresponding cell.

Heather Duff

@Jeremy Horton

I'm wondering if it was giving you an error because I didn't note the row number for the QTY column.

Try pasting this into the QTY column in rows 3-8: =sumif([Replace in Fiscal Year]$33:[Replace in Fiscal Year]$49,[Replace in Fiscal Year]@row,QTY$33:QTY$49)

Theoretically, you shouldn't have to change the formula when you switch rows - that's what's helpful about sumif. It should do the "picking and choosing" for you.

Let me know if this fixes it.

Heather Duff

Hi @Jeremy Horton ,

It should be able to be done by using this formula:

=sumif([Replace in Fiscal Year]33:[Replace in Fiscal Year]49,[Replace in Fiscal Year]@row,QTY:QTY)

This has it look within Replace in FY column, rows 33-49, and sum the Qty column for any rows that match Replace in FY in the row in which the formula sits.

Let me know if it works for you!

Best,

Heather

Jeremy Horton

I'm getting argument errors when applying. I may not be pasting the formula in the correct place. Attached is a wider view of the spreadsheet that might help.

Could the formula be updated where the QTY totals in rows 33-49 and based on the dropdown selection in "Replace in Fiscal Year" column, show the sum in rows 3-8 that correspond to the "Replace in Fiscal Year" cells?? So if cells 33G-49G have dropdown selection "2021/22", the sum of the cells in 33H-49H would show in cell 3H (I just used letters in lieu of column names). I could then update the formula for each of the other dropdown selections in "Replace in Fiscal Year" such as 2022/23, 2023/24, etc.. and those totals in the "QTY" cells would appear in the corresponding cell at the top for cells 4H, 5H, 6H, etc..

Hope that makes sense...

Heather Duff

@Jeremy Horton

I'm wondering if it was giving you an error because I didn't note the row number for the QTY column.

Try pasting this into the QTY column in rows 3-8: =sumif([Replace in Fiscal Year]$33:[Replace in Fiscal Year]$49,[Replace in Fiscal Year]@row,QTY$33:QTY$49)

Theoretically, you shouldn't have to change the formula when you switch rows - that's what's helpful about sumif. It should do the "picking and choosing" for you.

Let me know if this fixes it.

Jeremy Horton

That worked!!

Thank you very much for your help on this!!

Heather Duff

Happy to help! Glad it worked for you.