Countif - Not!

Jason P

All,

I may have gone next Lv here but the following basic counts results in 0. I've written first 2 then tried the new AI feature to confirm and same result, 0 count.. ? Ultimately I want to count how many orders Dale took in 2024 to current date

Sales Rep column is a drop down with up to 9 reps no multiples. Date of order is a Date column, no formulas in either or cell links.

=COUNTIF([Sales Rep:]:[Sales Rep:], ("Dale"))

=COUNTIF([Sales Rep:]:[Sales Rep:], CONTAINS("Dale", @cell))

AI wrote the below & I added for results from this year only, Same result 0.

=COUNTIFS([Sales Rep:]:[Sales Rep:], CONTAINS("Dale", @cell), [Date of Order:]:[Date of Order:], IFERROR(YEAR(@cell), 0) = 2024)

Appreciate being put back on the path..

AravindGP

Hi @Jason P

If your Sales Rep column is single select, you can try this formula to get the count

=COUNTIFS([Sales Rep:]:[Sales Rep:], "Dale", [Date of Order:]:[Date of Order:], IFERROR(YEAR(@cell), 0) = 2024)

Jason P

Thanks Aravind,

Yep tried that one, same 0 result, tried too HAS but again same result. It's frustrating as ultimately I'm looking to build a cross sheet from 2 or 3 sheets but thought I would start with a basic in a sheet summary figuring the data would come in handy, so far no love.

AravindGP

That's odd. It worked for me when I tested it. Is Dale the actual value displayed in the Sales Rep column? You can try creating a Year of Order column in there with the formula =YEAR([Date of Order]@row) to just pull the year. Use that column instead and try.

Once you've the column, you can use the formula =COUNTIFS([Sales Rep]:[Sales Rep], "Dale", [Year of Order]:[Year of Order], 2024)

Jason P

"actual value displayed", No, it contains his surname in some cells and not in others. Adding his surname to the formula worked and resulted in 26 entries. There are some cells where it's just Dale due to changes this year hence leaving the surname out and adding the CONTAINS however adding this or HAS to the below results in 0.

=COUNTIFS([Sales Rep:]:[Sales Rep:], "Dale Smith", [Date of Order:]:[Date of Order:], IFERROR(YEAR(@cell ), 0) = 2024)

AravindGP

Since you've a mix, Contains works best. Again, it works on my test. Try adding the Year helper column and using that =COUNTIFS([Sales Rep]:[Sales Rep], CONTAINS("Dale", @cell), [Year of Order]:[Year of Order], 2024)

Jason P

Might see if I can use data from another column to capture simular, not a fan of helper columns when the info I need is right there…. I'll keep plugging away. Thanks for you assistance.

On another. Are you experiencing any sheet lag (tab through Cells - looking at Attachments) since recent updates?