SUMIFS Value Error trying to work with multiple columns range

[jlawrence]

Hi, I am trying to automate a spreadsheet to calculate gross sales for each month, the are multiple brands I need to include that are on different rows. The months go by column so the range includes multiple columns which is why I think I am getting an error. The formula I am currently using is =SUMIFS('PL Updated'!$BT$8:$CI$28,'PL Updated'!$BT$7:$CI$7,$D$1). D1 refers to a cell with the month, so I was hoping that each month I could just put the new month into this cell and then the SUM would draw from the next column on the sheet with the sales. Do I need to use a different formula or an array? Thank you in advance!

 

[Toadstool]

Hi JLawrence,

Are $BT$7:$CI$7 really months names (e.g. "February") or are they actually dates which are formatted as month?

SUMIFS will give you a #VALUE error because the range dimensions don't match. You could use SUMPRODUCT instead.

Book2
	D																																																																		BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI
1	October-21																																																																			Total Sales
2																																																																				87
3
4
5
6
7																																																																				Product	January-21	February-21	March-21	April-21	May-21	June-21	July-21	August-21	September-21	October-21	November-21	December-21	January-22	February-22	March-22	April-22
8																																																																				ACME Widgets	1	4	7	10	13	16	19	22	25	28	31	34	37	40	43	46
9																																																																				Smith Bits	2	5	8	11	14	17	20	23	26	29	32	35	38	41	44	47
10																																																																				Brandx	3	6	9	12	15	18	21	24	27	30	33	36	39	42	45	48
PL Updated

Cell Formulas
Range	Formula
BS2	BS2	=SUMPRODUCT(('PL Updated'!$BT$8:$CI$28*('PL Updated'!$BT$7:$CI$7=$D$1)))
BU7:CI7	BU7	=EOMONTH(BT7,0)+1

 

[jlawrence]

Hi JLawrence,

Are $BT$7:$CI$7 really months names (e.g. "February") or are they actually dates which are formatted as month?

SUMIFS will give you a #VALUE error because the range dimensions don't match. You could use SUMPRODUCT instead.

Book2
	D																																																																		BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI
1	October-21																																																																			Total Sales
2																																																																				87
3
4
5
6
7																																																																				Product	January-21	February-21	March-21	April-21	May-21	June-21	July-21	August-21	September-21	October-21	November-21	December-21	January-22	February-22	March-22	April-22
8																																																																				ACME Widgets	1	4	7	10	13	16	19	22	25	28	31	34	37	40	43	46
9																																																																				Smith Bits	2	5	8	11	14	17	20	23	26	29	32	35	38	41	44	47
10																																																																				Brandx	3	6	9	12	15	18	21	24	27	30	33	36	39	42	45	48
PL Updated

Cell Formulas
Range	Formula
BS2	BS2	=SUMPRODUCT(('PL Updated'!$BT$8:$CI$28*('PL Updated'!$BT$7:$CI$7=$D$1)))
BU7:CI7	BU7	=EOMONTH(BT7,0)+1

I am using dates formatted as months similar to what you have shown. I will try this out, thanks for responding!

 

[Toadstool]

You're welcome!
Then as long as the date match is exact it will work.