I need to count the number of units with IDs that begin with a T and cannot figure out why the formula below is not working. I'm also including a workbook with my data.
Formula = =IF(LEFT($A$13:$A$6769,1)="T*",COUNTIFS(G13:G6769,"FL",$D$13:$D$6769,"",$F$13:$F$6769,$A2),"")
ATA.xlsb
Are you trying to use an array formula?
An alternate method is to add a column (in column I) that is =left(SRV Code, 1)
And use this =countif(I:I,"T")
Hi,
Your data doesn't seem to match your suggested result.
Filtering column a for anything beginning with 'T' only results in 13 rows and any additional filters for say "FL" would only reduce this - as it happens there aren't any. So I can't see how you arrive at a count of 98 for B2.
Please clarify.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
There are only 14 rows where the ID starts with 'T' and none of those rows have FL in column G so I'm not sure where you get the 98 you mention in the workbook.
If you just wanted to count the no of rows with ID starting with 'T' you could use this.
Formula:
Please Login or Register to view this content.
If posting code please use code tags, see here.
Or this array formula
=SUM(IF(LEFT($A$13:$A$6769)="T",1))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are lots of errors in F column, due to blank cells in E column. Use filter to find cells with errors and fix it.
Quang PT
Please remember that the formula COUNTS occurances with multiple criteria. IDs that start with a T, matches the month from table in column A, and column G matches the header (FL).
As others have told, only 14 rows have ID starting with T. How is 98 the answer you wish?
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
The original data contained 98, I shortened it because there are over 37k lines of data.
Fair enough. We in fact encourage you to cut out non significant data, but don't you think it would have been a good idea to change your comment so that it reflected the data you were sharing with us.Originally Posted by DoodlesMama
We might then not have wasted a lot of time puzzling over inconsistent data.
Try this one
=SUM(IF(LEFT($A$13:$A$6769)="T",IF($G$13:$G$6769="FL",1)))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks