Timeline for How to exclude strings that end in any number when using COUNTIF?
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 24, 2021 at 8:35 | answer | added | Jeorje | timeline score: 0 | |
| Oct 19, 2017 at 20:45 | comment | added | gns100 | I'm pretty sure the array has to be statically declared in the formula. I've tried to put the array in a cell and then reference the cell, but couldn't get it to work. Maybe someone out there knows how to do it. | |
| Oct 17, 2017 at 0:21 | comment | added | JAT86 | @gns100 Thanks, the formula works. Is it possible to use references (A2, A3) in the array instead of text since I would want to fill the column with that formula. I tried doing this but it won't work: =SUM(COUNTIFS(A2:A11,{""&A3,""A3&" *"})) | |
| Oct 16, 2017 at 23:00 | comment | added | gns100 | If it can be assumed that what you are looking for ends with "Area 25" or has "Area 25"+space+additional, then try this: =SUM(COUNTIFS(A21:A11,{"*Area 25","*Area 25 *"})) If you are not familiar an array can be used to capture note all of the alternatives, then the sum is counting each answer from the array. | |
| Oct 15, 2017 at 22:05 | history | edited | JAT86 | CC BY-SA 3.0 |
Removed sentence which adds some confusion.
|
| Oct 15, 2017 at 21:55 | comment | added | JAT86 | @Bandersnatch Sorry for being confusing. I have added a screenshot and added some details. Please check. Thank you. | |
| Oct 15, 2017 at 21:53 | history | edited | JAT86 | CC BY-SA 3.0 |
Changed formula to match the formula in added screenshot.
|
| Oct 15, 2017 at 4:44 | comment | added | Bandersnatch | Yes, that's what this formula does, except I thought you wanted to exclude cells that end with a number - like Area 25. | |
| Oct 15, 2017 at 1:26 | comment | added | JAT86 | @Bandersnatch Thanks for the response, but what I am trying to achieve is to count how many times a string (like "Area 25") occurs in a column. | |
| Oct 14, 2017 at 16:51 | comment | added | Bandersnatch |
Something like this should work: =SUMPRODUCT(1*(ISERROR(VALUE(RIGHT(G1:G10))))). I wasn't able to test this with a named table/column, though
|
|
| Oct 14, 2017 at 7:05 | history | asked | JAT86 | CC BY-SA 3.0 |