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

Options

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

Answers

  • Adrian Backus
    Adrian Backus ✭✭✭✭
    Options

    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
    Frank Dean ✭✭✭
    Options

    @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!