How to cross reference two columns from one sheet to a data sheet. Counting by two data points.

Frank Dean

Hi All,

I am trying to count the number of product types (Column A), by Project Manager (Column D) and am always getting incorrect argument or inparseable. I can get the data if I just count the product types, but not when I want to only count product types by PM. I am putting this formula in a separate sheet (formula sheet) I have been using to get data, so I get to reference another sheet (the assignment sheet). See examples of what I have tried below (renamed actual names/products to fake ones for privacy):

=COUNTIFS({PM Assignment Sheet Range 3}, "Gizmo A", "John Doe")

=COUNTIFS({PM Assignment Sheet Range 3}, "Gizmo A", {PM Assignment Sheet Range 1}, "John Doe")

=COUNTIFS({PM Assignment Sheet Range 3}, "Gizmo A", {PM Assignment Sheet Range 1}, OR({PM Backlog 2020 DU V2 Range 1}@cell = "John Doe"))

Help! =D

Adrian Backus

You have to have two arguments per parameter that you want to match so you should wind up with 4 arguments

For more on the Syntax: https://help.smartsheet.com/function/countifs

It looks like the second formula you listed is probably close to correct but I suspect that you need to make sure that your references are pointing at the actual column and not an array of multiple columns.

Therefore if you use:

=COUNTIFS({PM Assignment Sheet Range 3}, "Gizmo A", {PM Assignment Sheet Range 1}, "John Doe")

{PM Assignment Sheet Range 3} needs to be [Column A]:[Column A] and {PM Assignment Sheet Range 1} needs to be [Column D]:[Column D]

Frank Dean

@Adrian Backus Thank you for your help! So, the PM Assignment Sheet Range was over multiple columns like you suspected. I fixed to point to the actual column on both data points and it worked!

FYI - When I tried [Colum A]:[Column A] that only points to the columns I am working on in the data sheet, not the sheet I need to pull from. But still good to know!

Resolved.