Forum Discussion

Mrbiddy's avatar
Mrbiddy
Copper Contributor
Sep 27, 2024

Help with Countifs function please!

Hi, 

 

I have created a summary table for persons and a quantity from another column. But within a table inside the excel sheet. 

 

I need to be able to count if column G - named Opportunities_table[Estimator] has ''James'', for example and column R named Opportunities_table[DEAL STAGE] one of the following words ''Submitted'' ''Negotiating'' ''Closed won'' ''Closed Lost'' but not ''Pricing'' ''Declined''. 

 

Currently have the formula as:

=COUNTIFS(Opportunities_table[ESTIMATOR],"JAMES",Opportunities_table[DEAL STAGE],"<>Declined""<>Pricing")

 

But it seems to be still counting them all and not excluding Declined and Pricing. 

 

Please help! Thank you!

  • Mrbiddy 

    Your deal stage is interpreted by Excel as

    <>Declined"<>Pricing

    which it fails to find. Try

    = COUNTIFS(
        Opportunities_table[ESTIMATOR],  "JAMES",
        Opportunities_table[DEAL STAGE], "<>Declined",
        Opportunities_table[DEAL STAGE], "<>Pricing"
      )

     

  • Mrbiddy 

    Your deal stage is interpreted by Excel as

    <>Declined"<>Pricing

    which it fails to find. Try

    = COUNTIFS(
        Opportunities_table[ESTIMATOR],  "JAMES",
        Opportunities_table[DEAL STAGE], "<>Declined",
        Opportunities_table[DEAL STAGE], "<>Pricing"
      )

     

    • Mrbiddy's avatar
      Mrbiddy
      Copper Contributor
      Thank you!

      This fixed it straight away!

Resources