sum calculating by different types

[Nils_Junker]

Hi everybody;

is there a chance to calculate a sum of different types without filtering them first?
Here a screenshot

now i want to build the sum of all "CLD", "HSE" and so on per month, without filtering the data by storage type.
is there a formula to do that?
Thanks for your answers!

 

[awoohaw]

You can use the SUMPRODUCT. But, I don't understand what you mean by wanting to get the SUM and not wanting to FILTER. In order to get a total sum some kind of filtering must be done (even pivot tables filter).

Book2
	A	B	C	D	E	F	G	H
1	Storage Type	2024-01-01	2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01
2	GEN	5	8	9	7	7	8	8
3	HSE	7	6	7	8	9	7	7
4	CLD	6	7	5	7	6	8	8
5	GEN	9	6	7	5	5	7	7
6	HSE	6	5	8	7	5	8	5
7	CLD	5	6	8	9	6	6	7
8	GEN	6	9	5	8	6	9	9
9	HSE	7	8	9	9	9	9	8
10	CLD	6	9	7	5	9	5	7
11	GEN	5	8	7	8	7	8	9
12	HSE	5	6	8	5	9	9	5
13	CLD	7	8	9	8	8	7	7
14
15	Storage	GEN	CLD
16	Month	2024-03-01
17	SUMPRODUCT	28
18	2 Storages SUMPRODUCT		57
Sheet5

Cell Formulas
Range	Formula
B17	B17	=SUMPRODUCT(($B$15=$A$2:$A$13)*($B$16=$B$1:$H$1)*($B$2:$H$13))
C18	C18	=SUMPRODUCT((($B$15=$A$2:$A$13)+($C$15=$A$2:$A$13))*($B$16=$B$1:$H$1)*($B$2:$H$13))

 

[Peter_SSs]

For the future, this would make it easier for people to help you.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Could you use Excel's built-in Pivot Table feature? Here is a small example

24 02 15.xlsm
	A	B	C	D	E	F	G	H
1	storage type	1/02/2024	1/03/2024	1/04/2024	1/05/2024	1/06/2024	1/07/2024	1/08/2024
2	GEN	8	3	2	1	3	7	9
3	GEN	9	3	7	7	5	8	3
4	GEN	7	8	6	1	8	1	8
5	CLD	6	4	9	6	8	2	9
6	GEN	7	4	3	5	5	6	4
7	GEN	5	8	6	8	2	7	2
8	GEN	1	2	3	5	8	6	2
9	CLD	7	5	8	9	3	6	6
10	HSE	3	6	3	6	2	3	5
11	HSE	9	7	1	3	3	4	7
12	GEN	7	6	1	5	6	9	8
13	GEN	4	1	2	6	4	2	1
14	GEN	9	3	1	4	9	6	5
15	GEN	1	6	1	2	6	6	1
16	GEN	3	5	9	6	1	1	5
17	HSE	3	5	4	5	4	5	1
18	GEN	2	3	4	2	3	2	5
19	GEN	6	1	3	5	5	8	4
20	HSE	8	1	3	2	7	6	9
21
22
23
24	Row Labels	Sum of 1/02/2024	Sum of 1/03/2024	Sum of 1/04/2024	Sum of 1/05/2024	Sum of 1/06/2024	Sum of 1/07/2024	Sum of 1/08/2024
25	CLD	13	9	17	15	11	8	15
26	GEN	69	53	48	57	65	69	57
27	HSE	23	19	11	16	16	18	22
28	Grand Total	105	81	76	88	92	95	94
Sheet4

 

[Nils_Junker]

You can use the SUMPRODUCT. But, I don't understand what you mean by wanting to get the SUM and not wanting to FILTER. In order to get a total sum some kind of filtering must be done (even pivot tables filter).

Book2
	A	B	C	D	E	F	G	H
1	Storage Type	2024-01-01	2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01
2	GEN	5	8	9	7	7	8	8
3	HSE	7	6	7	8	9	7	7
4	CLD	6	7	5	7	6	8	8
5	GEN	9	6	7	5	5	7	7
6	HSE	6	5	8	7	5	8	5
7	CLD	5	6	8	9	6	6	7
8	GEN	6	9	5	8	6	9	9
9	HSE	7	8	9	9	9	9	8
10	CLD	6	9	7	5	9	5	7
11	GEN	5	8	7	8	7	8	9
12	HSE	5	6	8	5	9	9	5
13	CLD	7	8	9	8	8	7	7
14
15	Storage	GEN	CLD
16	Month	2024-03-01
17	SUMPRODUCT	28
18	2 Storages SUMPRODUCT		57
Sheet5

Cell Formulas
Range	Formula
B17	B17	=SUMPRODUCT(($B$15=$A$2:$A$13)*($B$16=$B$1:$H$1)*($B$2:$H$13))
C18	C18	=SUMPRODUCT((($B$15=$A$2:$A$13)+($C$15=$A$2:$A$13))*($B$16=$B$1:$H$1)*($B$2:$H$13))

thanks, i did it otherwise with the formula =SUMIF