SumProduct Quarterly from Monthly Data

[StarMusk10]

Hello Gurus,

I have read multiple threads regarding this issue, but have not figured out what to do when our fiscal year starts in October instead of January. I have a data table that monthly data is entered into and would like to be able to calculate sums based off a specific unit, fiscal year, and quarter. I have attached a sample file for you to look at. Your help is always appreciated!

Thanks,
StarMusk10

 

[r1c1]

@StarMusk10

You do not need SUMPRODUCT for this. You can use SUMIFS (which offers simpler syntax). For example, this formula will get "None" values for Fy19 Q1 in Unit "1".

=SUMIFS(C$2:C$55,$B$2:$B$55,">=1-Oct-2018",$B$2:$B$55,"<1-Jan-2019",$A$2:$A$55,1)

Please find attached file with working formulas.

All the best.

 

[StarMusk10]

r1c

@StarMusk10

You do not need SUMPRODUCT for this. You can use SUMIFS (which offers simpler syntax). For example, this formula will get "None" values for Fy19 Q1 in Unit "1".

=SUMIFS(C$2:C$55,$B$2:$B$55,">=1-Oct-2018",$B$2:$B$55,"<1-Jan-2019",$A$2:$A$55,1)

Please find attached file with working formulas.

All the best.

r1c1
I love this site! Thank you very much for your fix on this.

I was initially trying to use a formula in the monthly data to tell me the FY and Qrt, but when I would try to use the output of the formula I would always end up with 1905 as a year. After doing some research, I learned why that was happening and figured it would be simpler to just set up a table with the FY and Qrts already populated, that way I could use a SUMPRODUCT or XLOOKUP to get the data I need for the dashboard (oh yeah, this is for a dashboard), but I still couldn't get a formula to work to sum the data by qrts, that's when I put up the Bat-signal and messaged you guys! Any thoughts on adding the FY & Qrts to the monthly data, or am I heading in an ok direction in regards to the SUMPRODUCT or XLOOKUP?

Thanks again,
StarMusk10