Sum column based on two other columns criteria

[tessam]

Hello,

I am having problems with a formula that would add a column if it matchs two other column criteria.
R A T I O' S
Code Time Time Hours Gladys Roger Rick
AOC 8:00 12:00 4 3 3 3
A0C 12:01 2:00 2 2 0 2
APC 2:00 4:00 2 1 0 0
APC 4:01 8:00 4 3 3 3

What I want to sum is the hours as long as the match the code and ratio for each person. So for AOC I would need a total for 2 and total for 3 and the same for APC total for 1 and 3. I can do a sumif based on one column but need to have it based on two. The code and the ratios to add the hours. Any suggestions would be helpful. Thanks Tessa

 

[SkipVought]

Hi,

What TOTALS are you referring to???

Please post what RESULTS you expect for a given criteria?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[AnotherHiggins]

Tessa,

Please provide more info. Posting a table can be tricky on Tek-Tips - you have to use [ignore][tt][/tt][/ignore] tags around the data for it to display correctly. U have tried to recreate your table below:
[tt]
| | | | R A T I O S |
Code | Time | Time | Hours | Gladys | Roger | Rick |
AOC | 8:00 | 12:00 | 4 | 3 | 3 | 3 |
A0C | 12:01 | 2:00 | 2 | 2 | 0 | 2 |
APC | 2:00 | 4:00 | 2 | 1 | 0 | 0 |
APC | 4:01 | 8:00 | 4 | 3 | 3 | 3 |[/tt]

(I took the liberty of removing the apostrophe in 'Ratios' because I doubt if it is possessive.)

Please confirm if that is what your data looks like.

Assuming it is - what totals would you expect for the groups you've defined? And what happens if the numbers for the three names is different? Example:
[tt]
| | | | R A T I O S |
Code | Time | Time | Hours | Gladys | Roger | Rick |
AOC | 8:00 | 12:00 | 4 | 3 | 2 | 1 |[/tt]

Bottom line - please provide more information about what it is you are trying to accomplish.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[tessam]

Thanks for the reply.

The above table is correct. For each row under the names it would be the same number. Its 1 staff to 3 persons and that number goes under the name who would need that staff. If its 1 staff to 2 the number would go under the name who would need that staff and the other would be a zero. If its 1 staff to 1 the number would go under the name who would need that staff and the other two would be zero. I have to do a whole week of times and who would need what staff ratio. I also need it to compare to the code. Each code has staff ratio 1:1, 1;2, 1:3 etc. So I would want to have it total the hours when it matches the code and the ratio number. So for Gladys for AOC, 1:3, equals 4, 1:2 equals 2. For Gladys APC, 1:3, equals 4, 1:1, equals 2. I would do this for each person to get the total hours for the whole week.