I am trying to count (or sum) columns and found help in a downloaded calc file called “functions_ifs.ods.”
It has Branch labels of “Town AA”, “Town BB”, or “Town CC”, then lists an example like this:
=COUNTIFS(Car_model,"Model 04",Branch,"City BB")
I need to know how to match a partial string.
So in the Branch column, if the word “City” could be any number of words, e.g. the “AA” ones could be “Town AA”, “District AA”, “City AA”, or just “AA”. How would I still match to the column containing “AA”, as opposed to an exact match?
The real world reason is that I have a date column, which has a format like this:
“Tuesday, May 21”
And I want to count all the Tuesdays where another corresponding column is non-zero (or non-blank).
I enabled wild cards, so I tried this:
=COUNTIFS(date,"Monday*",returned,"<>0")
To increment a count where the Date column contains “Monday” and the Returned column is not zero (or blank). I also disabled “Search criteria = and <> must apply to whole cells”.
But it does not work.
UPDATE:
I was able to get the count correctly by NOT using column name, and instead using cell range, like this:
=COUNTIFS(A9:A140,"Monday",J9:J140,"<>0")
Perhaps it cannot recognize my column labels for some reason…
I thought I could use the same with SUMIFS, where I simply change the column ‘J’ to ‘G’, because if Col A matches “Monday” and Col G is non-zero, I want to sum Column G. A bit more research showed I seem to need to tell it what to sum as first parameter, so I used this:
=SUMIFS(G9:G140,A9:A140,"Monday",G9:G140,"<>0")
Finally got that working too, so I seemed to have answered my own post. But I would still like to know why I was not able to use “Monday*” with wild card. I had to change the options to disable “Search criteria = and <> must apply to whole cells”. And also why I had to use a column range instead of a name. Thanks!
ToS Options.ods (279.7 KB)
