Simplified SUMIF

exceldotcom

New Member
Joined
Jul 2, 2018
Messages
15
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 FY19Q2 FY19Q3 FY19Q4 FY19
ExperiencedExperiencedExperiencedExperienced
JuniorExperiencedExperiencedExperienced
JuniorJuniorExperiencedExperienced
JuniorJuniorJuniorExperienced
JuniorJuniorJuniorJunior
New HireJuniorJuniorJunior
New HireNew HireJuniorJunior
New HireNew HireNew HireJunior
New HireNew HireNew HireNew Hire

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

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

Thanks all!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822
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
Joined
Jul 2, 2018
Messages
15
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
Joined
Aug 18, 2015
Messages
10,822
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,867
Messages
5,638,757
Members
417,051
Latest member
arthur_w

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top