Sumif on data where criteria are mixed on columns and rows

[korhan]

Hi! I have a data where I am trying to do a YTD calculation but my criteria are both on rows and columns. I am trying to do a YTD sum based on Year and Item columns. However, the problem is that my months are on the columns. Is it possible to do a YTD calculation, for instance, Year = 2022 & Item = 2, months Jan - Jun.

Year	Item	Jan	Feb	Mar	Apr	May	Jun	Jul
2021	1	10	11	9	8	12	0	3
2022	2	5	7	8	9	12	11	9

 

[RobP]

Hi, apologies I couldn't think of a solution for you right now. BUT, there is a function coming, thats in Beta Testing for O365 (if thats the version of Excel you are running today) that could be perfect for you in future when it does actually arrive mainstream. (apologies I've no idea when that could be.. maybe others on here might know more..)

Assumptions:
YEAR = col A, Jul = Col I, and therefore I assume Dec = Col N. Headers are in Row 1, and I used 3 rows of test data below.

This example would require you to type in your desired year (2022) into cell P1. Then your Item no. (2) into Q1. Finally you need to enter a date within the month of interest (so 01/06/22 for June ?) into R1.
Where I have "4" rows in my data, you'd also have to correct for your dataset length (rows) - so N4 would be N500 or whatever in your case etc.

=SUM(DROP(FILTER($C$1:$N$4,($A$1:$A$4=$P$1)*($B$1:$B$4=$Q$1),0),-12-MONTH(R1)))

Again, apologies it won't work today - but in future.. hopefully you can come back to it as soon as DROP() gets launched. If you don't have O365.. then maybe you'd also need to invest !

Essentially it creates an array of 12 columns of data (=each month) of the rows that match your criteria (2022, and 2). Then it uses the DROP() function to knock off the last 6 columns (ie. -12-6 where the 6 gets generated by the month() number of your inputted month date in R1)
So you end up with 6 columns instead, and simply SUM them all together.


cheers
Rob