# Simplified SUMIF

#### exceldotcom

##### New Member
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:

 Q1 FY19 Q2 FY19 Q3 FY19 Q4 FY19 Experienced Experienced Experienced Experienced Junior Experienced Experienced Experienced Junior Junior Experienced Experienced Junior Junior Junior Experienced Junior Junior Junior Junior New Hire Junior Junior Junior New Hire New Hire Junior Junior New Hire New Hire New Hire Junior New Hire New Hire New Hire New Hire

<colgroup><col span="4"></colgroup><tbody>
</tbody>

<colgroup><col span="4"></colgroup><tbody>
</tbody>

Thanks all!

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Eric W

##### MrExcel MVP
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.

Last edited:

#### exceldotcom

##### New Member
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

##### MrExcel MVP
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.

Last edited:

Replies
3
Views
453
Replies
0
Views
216
Replies
5
Views
3K
Replies
3
Views
671
Replies
3
Views
467

1,128,110
Messages
5,628,761
Members
416,337
Latest member
tl3phd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back