Formula to Use
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 2429 is where the totals would populate based on the matching of the "Replace in Fiscal Year" selected and "QTY" sum in the corresponding cell.
Best Answer

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 38: =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.
Answers

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 3349, 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

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 3349 and based on the dropdown selection in "Replace in Fiscal Year" column, show the sum in rows 38 that correspond to the "Replace in Fiscal Year" cells?? So if cells 33G49G have dropdown selection "2021/22", the sum of the cells in 33H49H 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...

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 38: =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.

That worked!!
Thank you very much for your help on this!!

Happy to help! Glad it worked for you.
Help Article Resources
Categories
Check out the Formula Handbook template!