Forum Discussion

ltsimmons's avatar
ltsimmons
Copper Contributor
Mar 07, 2022

Countif

I need help with a formula:  In cell with ?...If column "A" matches "A2" and matches "C1", count column "C".   The return should be 4

 01
11/1/2021 ?
   
   
11/1/2021Mon0
11/1/2021Mon1
11/1/2021Mon1
11/1/2021Mon1
11/1/2021Mon1
11/8/2021Mon2
11/8/2021Mon4
11/8/2021Mon4
11/8/2021Mon4
Formulas and Functions

5 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Mar 07, 2022
    Do you mean where column A matches A2 and column C matches C1?

    =SUMPRODUCT((A5:A13=A2)*(C5:C13=C1))

    or, you might also use countifs
    =COUNTIFS(A5:A13, A2, C5:C13, C1)
    • ltsimmons's avatar
      ltsimmons
      Copper Contributor
      Mar 07, 2022

      OliverScheurich  Thank you much...I'm still not sure why this doesn't work...could it be my column "C" is formatted for "Hour"?

       

       

       

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Mar 07, 2022

        ltsimmons 

        Does it work when you open the attached file?

         

        I assume you don't work with the german version of Excel and that's why SUMMENPRODUKT returns the #Name error in your sheet.

         

        If you work with the english version of Excel you can enter formula:

        =SUMPRODUCT((MONTH(A5:A13=C1)*(A5:A13=A2)*C5:C13))