formula help for this dataset?

Copper Contributor
Hi , I have this sheet here (but I need it in excel) & I need to do the following :
- Get the total of each unique "code" in "C" column by subtracting the non total numbers in column F.
Please see a demo sheet here with example of me done it manually for 2 cells.
One can see the demo sheet here
Can you help with the formula for excel?
7 Replies


In I2, you have =F2-G3

In I13, you have =F13-F14

Why the difference G / F?

@HansVogelaar .   Its the way the calculation needs to be done .... Depending on the Column "C"... if you look at Column C, there are numbers /codes, For each code I need to know the total in the the row situated in column I , but sometimes there these codes appear more than once (2 times mostly), so I cant total duplicates, I need to subtract the duplicate looking at column E if there is a variable named 09: 500+ Employees  & then get the total in Column I in the same row


Does this make sense to you??


No, I don't understand why you subtract a value from column G in one case and from column F in another.

@HansVogelaar Actually that was my mistake.(thanks for pointing it out). G column data is irrelevant. I changed the calculation to F2-F3 now... I think now you will understand if you see the sheet. The Totals are always calculate using numbers in F column if you see my examples..... any idea how I could calculate it for the entire sheet of 100k rows?



In I2:


=IF(RIGHT(E2, 5)="Total", F2-SUMIFS($F$2:$F$100000, $C$2:$C$100000, C2, $E$2:$E$100000, "<>*Total"), "")


Fill down.

@HansVogelaar Thanks , works in the google sheet as needed, but same formula in excel with same data/format/order, it throws up weird -ve nbrs... any idea why?


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?