# formula help for this dataset?

Copper Contributor

# formula help for this dataset?

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

# Re: formula help for this dataset?

In I2, you have =F2-G3

In I13, you have =F13-F14

Why the difference G / F?

# Re: formula help for this dataset?

@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??

# Re: formula help for this dataset?

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

# Re: formula help for this dataset?

@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?

# Re: formula help for this dataset?

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.

# Re: formula help for this dataset?

@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?

# Re: formula help for this dataset?

@StanCub

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?