countif in a table with formulas

I am sorry to ask but I have not found a clear answer to this concrete case.

I simply have a table... in my case I have dates eg 2022-10-01, 2022-11-01, 2022,-10-02, 2022-09-01, 2022-10-04 ---- and I called this Table1

I simply want to count the number of entries eg in October i.e something like

=countif(Table1, month(Table1[@])=10)

But

1) the formula above returns 0... although if I use it =MONTH(Table1[@]) in a separate column it will bring the correct result (and then I calculate sumif on this column )

2) a formula like =COUNTIF(Table4,MONTH(Table4[Column1])=10) expands the value zero to rows below....

Any hint please how I can use it?

TIA!

excel

Formulas and Functions

dscheikey
Nov 15, 2022
luispsimoes
With the following formula you get your desired result:

=SUM(--(MONTH(Table1[Date])=10))
Please adjust table name and column header.

dscheikey
Bronze Contributor
Nov 15, 2022
luispsimoes
With the following formula you get your desired result:

=SUM(--(MONTH(Table1[Date])=10))
Please adjust table name and column header.
- luispsimoes
  Copper Contributor
  Nov 15, 2022
  uau!... I would have never come to this one!.... man!......
  
  Still looks a mystery... I would think the SUMIFS was the one to go.
  but YOU NAILED it
  
  Thanks!!!
  - luispsimoes
    Copper Contributor
    Nov 15, 2022
    as an add on - why doesn’t the criteria work on the countifs?
    
    seems the criteria can’t evaluate expressions - just make simple comparisons….
    
    is this a limitation of my version or is it excell?
    
    thanks!

Forum Discussion

countif in a table with formulas

Share

Resources