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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Account[/TD]
[TD]jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3183-001-01[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD]70[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]100[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3183-001-01[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3183-001-01[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3184-001-01[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3184-001-01[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.