CODEWITHSUNDEEP
excel
nilsenfacE
nilsenfacE

Reputation: 59

Trouble using SUMIF

So basically, I have two sheets in Excel. One which says which subject a student has worked on, how many hours and day/night-time (Not that relevant to this case).

For the second sheet I would like to use a formula to summary how many hours in total a student has spent across both subjects. So it should look something like this.

For this example I have reduced to only three students. But the general idea is that there are about 80 students, where some students do a lot of work in one subject, but not necessarily has the same amount of hours in another one.

I have tried to make a COUNTIF-statment, but I cannot seem to make it count correctly. So any tips to make this formula work, would be greatly appreciated. Cheers :)

**Sheet 1:**<br>
Summary of work done Monday:<br>
Student name - Type -   Hours of work<br>
Ahmed -        Night -  5,5<br>
Stella -       Day -    5,0<br>
Kristin -      Night -  4,0<br>

Summary of work done in Tuesday:<br>
Ahmed -        Day -    3,5<br>
Stella -       Night -  6,5<br>
Kristin -      Day-     4,0<br>

Summary of work done in Wednesday:<br>
Ahmed -        Day -    3,0
Kristin -      Day -    2,0

**Sheet 2**<br>
Student -  Total hours<br>
Ahmed -    11,5<br>
Stella -   11,5<br>
Kristin -  2<br>

Upvotes: 0

Views: 37

Answers (1)

elliot svensson
elliot svensson

Reputation: 603

First, I believe that you incorrectly typed "Countif" instead of "Sumif", since what you want is the sum of the values.

I believe that if you go under Ahmed on Sheet 2, and type this...

=sumif(

...then go to Sheet1 and click the entire column of cells that have names...

...then type a comma (,)...

...then push the left-arrow to select the cell containing Ahmed on Sheet2...

...then type another comma...

...then go to the sheet showing hours and select all the cells from Monday through Wednesday containing hours...

...then type a close-parenthesis ")" that you would have the equation you need. Make sure that the two times you selected columns have exactly the same top and bottom, and whenever somebody does more work, make sure those columns are getting bigger.

My way

I would approach this by making a separate column in your data on Sheet1 that shows which date we're talking about.

Then I would use Insert > Table to make Excel turn the data into a pre-formatted table.

Then finally I would use the Sumif function on top of Table1[name] and Table1[hours] to calculate the list, like this:

=SUMIF(Table1[name], $A2, Table1[hours])

...where $A2 is the cell near the equation where Ahmed's name is listed for the purpose of making a summary.

The benefit is that over time, as new hours are recorded, the Excel table will automatically grow your calculation downward and you won't have errors later.

Upvotes: 1

Related Questions