sum calculating by different types

Nils_Junker

Board Regular
Joined
Jun 2, 2023
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Hi everybody;

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

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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
ABCDEFGH
1Storage Type2024-01-012024-02-012024-03-012024-04-012024-05-012024-06-012024-07-01
2GEN5897788
3HSE7678977
4CLD6757688
5GEN9675577
6HSE6587585
7CLD5689667
8GEN6958699
9HSE7899998
10CLD6975957
11GEN5878789
12HSE5685995
13CLD7898877
14
15StorageGENCLD
16Month2024-03-01
17SUMPRODUCT28
182 Storages SUMPRODUCT57
Sheet5
Cell Formulas
RangeFormula
B17B17=SUMPRODUCT(($B$15=$A$2:$A$13)*($B$16=$B$1:$H$1)*($B$2:$H$13))
C18C18=SUMPRODUCT((($B$15=$A$2:$A$13)+($C$15=$A$2:$A$13))*($B$16=$B$1:$H$1)*($B$2:$H$13))
 
Last edited:
Upvote 0
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
ABCDEFGH
1storage type1/02/20241/03/20241/04/20241/05/20241/06/20241/07/20241/08/2024
2GEN8321379
3GEN9377583
4GEN7861818
5CLD6496829
6GEN7435564
7GEN5868272
8GEN1235862
9CLD7589366
10HSE3636235
11HSE9713347
12GEN7615698
13GEN4126421
14GEN9314965
15GEN1612661
16GEN3596115
17HSE3545451
18GEN2342325
19GEN6135584
20HSE8132769
21
22
23
24Row LabelsSum of 1/02/2024Sum of 1/03/2024Sum of 1/04/2024Sum of 1/05/2024Sum of 1/06/2024Sum of 1/07/2024Sum of 1/08/2024
25CLD139171511815
26GEN69534857656957
27HSE23191116161822
28Grand Total105817688929594
Sheet4
 
Upvote 0
Solution
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
ABCDEFGH
1Storage Type2024-01-012024-02-012024-03-012024-04-012024-05-012024-06-012024-07-01
2GEN5897788
3HSE7678977
4CLD6757688
5GEN9675577
6HSE6587585
7CLD5689667
8GEN6958699
9HSE7899998
10CLD6975957
11GEN5878789
12HSE5685995
13CLD7898877
14
15StorageGENCLD
16Month2024-03-01
17SUMPRODUCT28
182 Storages SUMPRODUCT57
Sheet5
Cell Formulas
RangeFormula
B17B17=SUMPRODUCT(($B$15=$A$2:$A$13)*($B$16=$B$1:$H$1)*($B$2:$H$13))
C18C18=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
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,224,862
Messages
6,181,466
Members
453,045
Latest member
Abraxas_X

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top