How to calculate value based on one or two rows based on data in another column

[soderlloyd]

Hello everyone -

Thank you for your help.

I am trying to calculate an "adjusted" value (column E) based on two other columns (C & D) (sample data attached - the full data has 200+ rows).

If column C = "Total", column E = column D. Easy. However, if column C = "8350019" or "8350000" then a calculation has to be done with data from 2 rows corresponding to the same person (highlighted).

Thank you again and I'm happy to explain more if needed!

-Laurel

 

[herofox]

hello,You can use this
=IF(AND(COUNTIF($B$2:B2,B2)>1,$C2="Total"),$D2-$D1+($D1/4),IF($C2="Total",$D2,""))

 

[soderlloyd]

Thank you for your response. I am trying to understand your formula, but it is very complex for me so I am not sure how to update it to help with this scenario that also happens (attached). Sometimes an employee will have 3 occurrences. Thanks!

 

[soderlloyd]

To clarify, in this example, I need to sum the two group IDs (8350000+8350019) and then use that value in the formula for the employee, i.e. =D25-SUM(D23:D24)+((SUM(D23:D24))/4). Thank you so much!!

 

[NARAYANK991]

Hi ,

You mention :

However, if column C = "8350019" or "8350000" then a calculation has to be done with data from 2 rows corresponding to the same person (highlighted).

What is this calculation ?
Then again you mention :

Sometimes an employee will have 3 occurrences.

What should be done in this case ?

It would help if instead of using an Excel formula , you can explain the outcomes in all possible cases , in plain English.

Narayan

 

[bosco_yip]

In E2, formula copied down :

=IF(C2="Total",D2-SUMIFS(D:D,B:B,B2,C:C,"<>Total")*3/4,"")

then,

copied to K2 and drag down.

Remark :

Please convert existing "Total Units" column from Text to Numeric value, by : Select D2:D12>> "Data" >>"Text to Columns" >>Finish

Regards
Bosco

 

[soderlloyd]

Thank you! This is really helpful!