SUM some of the columns by date

Hello dear Excel Experts!

I have the following table
Capture.PNG

Is there a formula for summing for the product in multiple columns in the given month?
I have tried with the formula in the picture but no success
Hope I have made myself clear....

Thanks a lot!

Rather than an image, please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi and thank you for the reply!

I have attached a file.
If there are still questions please reply.
Thanks again!

In D18, copied across and down:

=SUMPRODUCT((MONTH($C$4:$C$14)=ROW(D18)-17)*($D$3:$H$3=D$17),$D$4:$H$14)

You must change the headings in D17-F17 to PROD 1, PROD 2 and PROD 3 to match the main table.

Using the ROW function leaves the formula vulnerable to new row insertions. If you were to insert a new row 5 then ROW(D18)-17 becomes ROW(D19)-17 and you miss the result for month 1.

Using the ROWS function is more robust.

=SUMPRODUCT((MONTH($C$4:$C$14)=ROWS(D$18:D18))*($D$3:$H$3=D$17),$D$4:$H$14)

If you were to insert a new row 5 then ROWS(D$18:D18) becomes ROWS(D$19:D19) and it still evaluates to 1.

Hi,

Can I suggest you normalise your data and have columns for

Date
Prod Number ' values being Prod 1, Prod 2 ..etc
Value

Then use a Pivot Table, which is far more flexible and efficient, and group the dates by month.

Here is another way..
Enter formula in D18, copy across and down

Formula:

Please Login or Register  to view this content.

Please see attached file.

Thank you all for your answers!
@Richard Buttrey I know that normalising and using PivotTable would be the right choise but unfortunately this is not an option.

... and learned another thing also: MONTH(1&$C18)

Glad to have helped!

You're welcome. Thanks for the feedback!