Simplified SUMIF

[exceldotcom]

Hi everyone - Happy Monday!

Apologies in advance as this is a little convoluted. Wanted to see if anyone has advice on a formula I'm about to write in regards to a SUMIF.

Basically, I am working with 4 quarters of result data (result in my vernacular means a dollar payout for context). Each quarter's result is aligned to an unique identifier & each quarter's result is in a separate cell. Basically everything is well formatted.

(For context) Due to a unique mechanic there are 3 different payout grids with different basis point %s based on which grid you are on. The grid is decided by tenure status.

I have a formula that works out what an individual's status (Experienced/Junior/New Hire) is in any given quarter and whether the quarter in question is a transition quarter (can provide formula if needed).

(The crux) There are 12 unique scenarios for an individual in a year. And basically if an individual transitions in a quarter all prior results are placed at 0 and we only count moving forwards.

• If Individual A has $1000 payout in Q1 and $1000 payout in Q2, but in Q3 they transition from Junior to Experienced, that payout becomes $0 for previous quarters and in Q4 only the Q3 payout is reflected in previous payouts (this is needed for other mechanics).

I have assigned a number to each status, 1 to Experienced, 2 to Junior and 3 to New Hire. This creates a unique identifier that I can use to SUMIF where the payouts are to ensure only the payouts I want are taken. I.e. in my above scenario, Individual A's number would be 6 (2+2+1+1) and based on that number the formula would know just to sum Q3 and Q4 payouts for previous payouts. This will require a really long SUMIF formula that I'm sure isn't as simplified as it could be. Does anybody have any ideas or short examples I can use?

See below for table showing the difference scenarios for an individual:

[TABLE="width: 384"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD][TABLE="width: 384"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Q1 FY19[/TD]
[TD]Q2 FY19[/TD]
[TD]Q3 FY19[/TD]
[TD]Q4 FY19[/TD]
[/TR]
[TR]
[TD]Experienced[/TD]
[TD]Experienced[/TD]
[TD]Experienced[/TD]
[TD]Experienced[/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD]Experienced[/TD]
[TD]Experienced[/TD]
[TD]Experienced[/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD]Junior[/TD]
[TD]Experienced[/TD]
[TD]Experienced[/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD]Junior[/TD]
[TD]Junior[/TD]
[TD]Experienced[/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD]Junior[/TD]
[TD]Junior[/TD]
[TD]Junior[/TD]
[/TR]
[TR]
[TD]New Hire[/TD]
[TD]Junior[/TD]
[TD]Junior[/TD]
[TD]Junior[/TD]
[/TR]
[TR]
[TD]New Hire[/TD]
[TD]New Hire[/TD]
[TD]Junior[/TD]
[TD]Junior[/TD]
[/TR]
[TR]
[TD]New Hire[/TD]
[TD]New Hire[/TD]
[TD]New Hire[/TD]
[TD]Junior[/TD]
[/TR]
[TR]
[TD]New Hire[/TD]
[TD]New Hire[/TD]
[TD]New Hire[/TD]
[TD]New Hire[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks all!

 

[Eric W]

I'm not entirely sure what you want. If you're looking for a simple way to find your "identifier", try:

=SUM(COUNTIF(A2:D2,{"Experienced","Junior","New Hire"})*{1,2,3})


I'm sort of assuming that it takes at least 1 year to move from 1 level to the next, and that no one will go down a level.

 

[exceldotcom]

Your assumption is correct, you can only be one status per quarter and cannot go backwards! Apologies... it is a little difficult to explain all the interlocking pieces. The important part is summing some columns based on an individual identifier (a random pre-assigned number) and then the number from the example above based on the individual being Experience, Junior or New Hire.

I will give what you provided above a go. Thanks!

 

[Eric W]

I was also thinking of patterns like:

New Hire/Junior/Experienced/Experienced = 3/2/1/1 = 7

Junior/Junior/Junior/Experienced = 2/2/2/1 = 7

There may be other patterns that sum to the same value, I'm not sure if that's possible, or important to you.