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

#### soderlloyd

##### New Member
Hello everyone -

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

#### Attachments

• 9.2 KB Views: 3

#### herofox

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

#### Attachments

• 10.4 KB Views: 3

#### soderlloyd

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

#### Attachments

• 10.8 KB Views: 8

#### soderlloyd

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

##### Excel Ninja
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

##### Excel Ninja

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

#### Attachments

• 12.4 KB Views: 5

#### soderlloyd

##### New Member
Thank you! This is really helpful!