mercredi 18 mars 2020

Subtracting from total if adjacent cell has content

I'm looking to subtract from a total derived from the following:

Count the occurrences of the content in cell E2 =COUNTIF(B:B, E2)

I want to subtract 1 from that total IF an adjacent cell contains anything.

FOr example: B2 has content == E2, and C2 is NOT empty, subtract 1 from total B3 has content == E2, and C3 is empty B4 has content == E2, and C4 is NOT empty, subtract 1 from total

The end result would be =COUNTIF(B:B, E2)-2.

This seemed kind of ridiculous to explain in text so here is an example worksheet with a column expressing where I would like to see the formula, and a column with the expected output. Programatically this would be a very simple task, but doing it in a spreadsheet is new to me. I'm hoping this can be done.

https://docs.google.com/spreadsheets/d/15xEEyeJx1atsZiDDwCk57KfnbCcZbIavV-gyVF0xuCA/edit?usp=sharing

Also, column A and B can vary in length, so I would prefer something that adapts to the length of the column.

Aucun commentaire:

Enregistrer un commentaire