Formula to add criteria from multiple columns from another sheet

Desire

Hi,

I need to calculate how many times "Change" appears in another sheet that has the same Project ID - I want to calculate how many changes a specific project had.

I have 2 sheets: Project Plan and Craid log

Project Plan has 2 columns - "Task Name" with the word "Changes" in the line and "Description" where I need the formula

Craid log has 2 columns - "Project ID" and "Type" with dropdown options, but I only need "Change"

I need the "Description" column in the Project Plan to calculate all the "change" in the Craid log that has the same Project ID that is in the Project Plan and the Craid log.

Project Plan:

Craid Log:

Paul Newcome

Change the cell reference

=COUNTIFS({Craid Log | Project ID}, Description3, {Craid Log | Type}, CONTAINS("Change", @cell))

Scott Peters

Hi @Desire - You could try something like this:

=COUNTIFS({Craid Log | Project ID}, [Project ID]@row, {Craid Log | Type}, CONTAINS("Change", @cell))

I hope this helps!

Desire

Hi Scott,

Thanks, but I get #UNPARSEABLE

My project plan has a project id that is in the "Description" column, I need the formula to go to the Craid log and only pull the information relevant to the project id and where there it only has "Change" selected - I need to add up how many Changes were selected against that project id.

Paul Newcome

Change the cell reference

=COUNTIFS({Craid Log | Project ID}, Description3, {Craid Log | Type}, CONTAINS("Change", @cell))

Desire

@Paul Newcome - thank you so much, it is working now!

Paul Newcome

Happy to help. 👍️