SUM issues

[Jesper_m]

Dear all.

I have been working on for a while with a sum issue that I simply can't find a solution to.

I whant to be able to SUM the amount for account "3183-001-01" and for "Jan to Mar" but how do I do that ?

I have tried both "Sumproduct" and "Index" and Match" but I cannot make it sum both columns and rows.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Account	jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov
2	3183-001-01	10	20	30	40	50	60	70	80	90	100	110
3	3183-001-01	1	1	1	1	1	1	1	1	1	1	1
4	3183-001-01	2	2	2	2	2	2	2	2	2	2	2
5	3184-001-01	3	3	3	3	3	3	3	3	3	3	3
6	3184-001-01	4	4	4	4	4	4	4	4	4	4	4

<tbody>
</tbody>

Thanks in advance,

Jesper

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Account	jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov
2	3183-001-01	10	20	30	40	50	60	70	80	90	100	110
3	3183-001-01	1	1	1	1	1	1	1	1	1	1	1
4	3183-001-01	2	2	2	2	2	2	2	2	2	2	2
5	3184-001-01	3	3	3	3	3	3	3	3	3	3	3
6	3184-001-01	4	4	4	4	4	4	4	4	4	4	4
7
8	Account
9	3183-001-01
10	jan
11	Mar
12	69
104

Cell Formulas
Range	Formula
A12	=SUMPRODUCT((--(MONTH("1/"&$B$1:$L$1)>=MONTH("1/"&A$10)))*(--(MONTH("1/"&$B$1:$L$1)<=MONTH("1/"&A$11)))*($A$2:$A$6=$A9)*$B$2:$L$6)

 

[Jesper_m]

Thanks AlanY.

When I try to use the formel it returns an error

 

[SpillerBD]

His formula works for his example.
Did you populate the input cells, A9:A11 too?

 

[Jesper_m]

His formula works for his example.
Did you populate the input cells, A9:A11 too?

The issue where that my dates are in text format and not date format - but it works if I change it

 

[Aladin Akyurek]

In A12 control+shift+enter, not just enter:

=SUM(IF($A$2:$A$6=A9,IF((1&$B$1:$L$1)+0>=(1&A10)+0,IF((1&$B$1:$L$1)+0<=(1&A11)+0,$B$2:$L$6))))

Note 1. This refers to the set up of AlanY in post #2 .

Note 2. Items like Jan, Feb, etc. are considered to be text values.

 

[SpillerBD]

I guess I was late to read this https://www.mrexcel.com/excel-tips/excel-modern-array-formulas-debut-in-office-365/
The future Death to CTRL+SHIFT+ENTER !
It will make it that much more important when helping with array formulas to know the version. Eventually getting in the habit of not declaring an array formula with ctrl+shift+enter, when a user's version may still require it. Also different testing results.