CODEWITHSUNDEEP
google-sheets
Modermo
Modermo

Reputation: 1992

Calculating rows that match a month in the header

I'm trying to calculate the monthly totals for fruit that I record sales of weekly.

I have a table that records the above data.

I would like another table that calculates the SUM values for each month for that fruit. Because every month has a potentially different number of weeks, I need a formula to essentially cycle through the weeks for the particular fruit I'm interested in and SUM the values for that month dynamically.

I've tried a bunch of different things, like:

=IF(A3:A5="Banana", SUM(INDEX(C2:J2,,MATCH("January",C3:F3))))

...in the hopes that I could somehow:

  1. Search for a fruit in a table
  2. If found, run through the weeks and calculate monthly values

Spreadsheet here

Upvotes: 0

Views: 56

Answers (1)

JPV
JPV

Reputation: 27242

In B9 try this formula

=sum(filter(filter($C$3:$5, month($C$2:$2)=column()-1), $A$3:$A$5=$A9))

Fill down and to the right as far as needed.

Upvotes: 1

Related Questions