Formula to sum every 2 columns based on criteria found in previous column

If I'm understanding your request correctly, this would be one way...
=SUMPRODUCT($B$1:$B$21,--(MONTH($A$1:$A$21)=$H2))+SUMPRODUCT($D$1:$D$21,--(MONTH($C$1:$C$21)=$H2))+SUMPRODUCT($F$1:$F$21,--(MONTH($E$1:$E$21)=$H2))
just combining the three sumproducts.

Is there a reason you have 60 columns under the same format? I mean, if what you posted had everything going down columns A and B instead of A and B, C and D and E and F then you could use a simple single sumproduct like this...
=SUMPRODUCT($B$1:$B$63,--(MONTH($A$1:$A$63)=$H2))
or even this
=SUMPRODUCT($B$1:$B$63,--(MONTH($A$1:$A$63)=ROW(A1)))
then you don't need the helper column of numbers in H2:H13.

Hmmm, if you have 60 columns of data which can't be altered (30 of dates and 30 of numbers) I'm having trouble of figuring out a way to do it without 30 sumproducts other than maybe in a pivot table but I'm still not sure that will work without some type of consolidation.
Maybe someone else will stop by with a better recommendation.

try to group dates by month and sum appropriate values. this is a shi...ty job but...

in your example i see only 10 months, so... using PowerQuery:

Date	Sum
1	217
2	455
3	121
4	26
5	161
6	678
7	1387
8	333
9	441
10	171

I expect that something based on a pivot table will be much easier to use -- especially long term. I:

1) Copied C,D and E,F and pasted them beneath the existing data in A,B to get a single list of data.
2) Added a row at the top and entered row headers ("date" in A1 and "values" in B1)
3) Insert -> pivot table
4) Make date the row labels and sum of value the values field.
5) Select the dates column in the pivot table and group by month.

I also added another column with "a" for the original set of data in A,B; "b" for the data originally in C,D; and "c" for the data originally in E,F. I expect there is some reason the data exist in three columns, so this additional column will allow other pivot tables to recapture the three different "types" of data.

Unless there is some significant reason to not use pivot tables and other database tools, this looks like database work and, in the long run, will be easiest to manage if you set the original data up into a good database format. See recent discussion (along with some good links to other sites) here: https://www.excelforum.com/excel-new...ta-layout.html

If you still want a single formula method this is fairly convoluted. It requires some array coercion. It must be array entered.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

If you wish to eliminate the nested IFERROR cut the column references in half ... ie COLUMN($A$1:$F$1) to COLUMN($A$1:$C$1).

Like this ... still array entered.

Formula:

Please Login or Register  to view this content.

How about something on the lines of

=SUMIFS($B$1:$F$21,$A$1:$E$21,">="&DATE(2018,H2,1),$A$1:$E$21,"<="&EOMONTH(DATE(2018,H2,1),0))

Of course, this assumes that no single cell in the columns to be summed will contain values high enough to be incorrectly evaluated as date serial numbers. (43101 to 43465 for 2018).

just to throw my hat in the ring, incase you're still looking for a single SUMPRODUCT formula

in Cell L2:
=SUMPRODUCT($B$1:$F$21*(MOD(COLUMN($B$1:$F$21),2)=0)*(MONTH($A$1:$E$21)=H2)*(MOD(COLUMN($A$1:$E$21),2)=1))

You are welcome, Use POWER Query, Luke
Thanks for the feedback
Have a nice day