How to sum numbers with certain criteria in Excel

[Laalamani]

In a spreadsheet I have a list of different departments. In 12 other worksheets (one for each month) I have written down the number of cases reported from each department. There is one line per case and in a column it is written the number of hours spent on each case. In the first worksheet I've created the formulas that determine how many cases I have per month from each department, how many cases are treated as professional systems, etc. I would not mind finding out how many hours I have worked for each department every month. How do I sum multiple number of hours, first check if a given department name appears in another column than the one hour number is written?

[sergioKomic]

The only thing I can think of is DSUM, but this requires a bit of organization of the excel spreadsheet. If the feature works only 7 times (or was it 6?) after another. It would anyway have been a ridiculously long sentence with an if statement per line. Since I do not have the Office at home, so I have to look at it tomorrow. Is there really any easier solution than DSUM.

[Laalamani]

The organization of the worksheets should be pretty good. I've already used it is some other formulas and it worked perfectly ok. I've tried to be wise in DSUM in a couple hours and still realize that I do not see a way of how it works. I think I have done all wrong according to how it should be solved in Excel. Now this is just a suggestion from my side but how will the IF function work in this problem?

[Gunilla]

Excel and other spreadsheets are bad on the 3D-matrix calculation for to many sheets. When you have one sheet per month, you have a really good starting point for the summary per month, and a useless starting point for everything else (per company, per hour, per day, per activity,..). Excel works best when everything is under one another in list form in a single sheet. But it usually requires that you make separate presentation layer for data in and out.

[Laalamani]

So what you are saying is that it would be better to make these calculations on a monthly sheets so that everything will be "manipulated" by the Summary sheet? If you think it is best so as I go for a solution where the hours are added up at each track. What do you think about SUMIF, how should I use it? Can you help me how to use it with an example or so?

[Gunilla]

What I'm really saying is all data should be on a single sheet. But you must have months then make a summary where you are looking for per month per unit in the same cell on all sheets. Here is how to use SUMIF.

Syntax: SUMIF(range,criteria, sum range)

Description: Range - is the range of cells you want to evaluate.
criteria - the condition that defines which cells will be added together, in the form of a number, expression, or text. Conditions, for example, expressed as 32, "32",> 32 "," apples ".
sum rang - are the actual cells to be summarized.


and then adds the cross to end all C12 cells as follows (with month names as the sheet name)
= SUM (Jan: Feb: C12)