Counting only if conditions are fullfilled

Michaela Kamenska

Hi everyone,

I was wondering if someone can help me with a formula for counting stuff? I've got let's say 4 stages of study progress - start-up, active, LTFU and completed.

Now I've got a helper column to help me identify if the project code is unique as multiple resources may work on the same project. If it says "1" the code is unique, if it says "0", the code is already mentioned above in the sheet.

I would like to build a metrics sheet where I will count the projects by stage but only those that have "1" in the helper column.

My idea was to have something like this, but because I'm a bit of a noob, I'm not sure where I am making a mistake or how to properly format it:

=COUNTIF(AND({Master Projects and OOO Tracker Range 1}, [unique]@row=1, Label2))

Label 2 refers to the keyword in my metrics sheet, e.g. start-up, active, LTFU and completed.

Can anyone please advise?

David Joyeuse

Hi @Michaela Kamenska

I'm not sure I'm following you correctly here.

You mean that you want to count each different stages of progress that are unique in your sheet? If so, you're gonna need 4 formulas. One for each stage in fact. You can't do a countif that will count each of them at the same time.

Anyway your formula will be like this:

=COUNTIF([Study progress]:[Study progress], "Start-up", [unique]:[unique], 1)

Just replace Start-up by other values in the other formulas.

If that doesn't help, could you please show us a screenshot or two of what you're trying to achieve? With no sensitive or cnfidential datas on it obviously. That would be helpful.

Michaela Kamenska

Hi @David Joyeuse

so I have this kind of sheet (master projects and OOO tracker):

at the end of the sheet there is a "helper column" indicating if the Study # is unique.

I understand your formula, yet what I'm trying to have is a separate metrics sheet that will count live, based on the stage:

so I am not sure how to reference the sheet and set the conditions as well?

Basically in the second sheet (picture #2) I want to put a formula next to the relevant stage labels and the formula will be counting from the sheet in the picture #1.

Not sure if that's clear?

John Jonassen

If I may dig into your process a bit, it might help with the question/answer of your formula too.

Can each of your Studies be actively in multiple Stages (Example: Study # 0001 is in Start-Up AND Active Study at the same time, and such)

OR

Is a Study only allowed to be in 1 Currently Active Stage (Example: Study #0001 remains in Start-Up until marked as complete, then it moves to Active Study)?