COUNTIFS + and referencing two columns in another sheet

caitlinoreilly11

Hi - I am trying to reference another sheet with columns "placement or asset" and "designer" where I can reference the designer name AND when the placement column says placement and have it count this amount

Below is what I have done, but it is not working

=COUNTIFS({NEW Global Messaging 2018 Range 2}:{NEW Global Messaging 2018 Range], "NAME",Placement:Placement, "Placement")

Chris McKay

Hi Caitlin,

I just wanted to be clear about what you are after here before I jump in.

You would like the formula on your target sheet to return a COUNT of rows in your source sheet that match both the following criteria: 

• Designer column = "NAME"
• Placement column = "Placement"

I have a few questions about how your sheets are structured:

• You have attempted to reference only one range in your formula above, but it looks like you've accidentally created both a NEW Global Messaging 2018 Range 2 and a NEW Global Messaging 2018 Range. Is this correct? While you can definitely reference 2 ranges, like you have, I have a feeling this is not what you meant to do. What data does NEW Global Messaging 2018 Range actually contain? I'm guessing it references the Designer column containing the names of your designers, but I'm unsure.
• You're also missing a closing curly brace "}" in your formula, instead using a square bracket "]". Square brackets are used to reference columns or cells on the same sheets, while curly braces are used for named ranges on another sheet.
• The "NAME" criterion you have listed in your formula will be looking for  the value NAME in your Designer column (assuming the above point is correct). Are you wanting to do this, or are you looking to search for a name value contained in a cell on the target sheet and located somewhere in the Designer column?
• You mention "placement or asset" above, but there only appears to be a Placement column in your formula. Could you please clarify?

If you have a few screen shots of your sheets, that would be great.