Need to sum different types of absences

I have a list of absences and a calculation to work out how many working days were lost during a given period.

The absences are to be categorised as either 'Short' or 'Long'. A short absence is <= 20 and a long absence is => 21.

The aim is to have something like this

Absence Reason | Days Lost Short | Days Lost Long | Total Days Lost

The current solution is to categorise the absences into Short and Long using an IF statement. We then split the spreadsheet into two separate worksheets named Short and Long.

A separate spreadsheet is then set up with the list of absence types. We then use the SUMIF function

=SUMIF(Short!K:K,A2,Short!I:I)

This works but it seems very clumsy. Is there a more elegant solution to this?

Originally Posted by jeffreybrown

I wouldn't categorize the Sumif as clumsy, if it returns the results you desire it is probably the right choice for you. That is, unless it seems to be taking an extrodinary time to calculate. If this is the case other methods could be explored, but for now the Sumif seems appropriate.

A cleaner method though might be a pivot table.

By clumsy I meant that we had to go through various stages to reach a point where we could use SUMIF.

I'm not familiar enough with the syntax to know whether it would be possible to create a SUMIF statement that could look at the table and SUM absences that were of a certain type and met a defined criteria for number of days. E.g. if the absence type is Anxiety and the number of days lost is equal to or less than 20 then sum those absences and return the number.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!