Excel countif formula two conditions

0
votes

I am having difficulties with the countif formula, because I need to count if a certain sheet has values that start with the number "2" but only if its length is equal to "9".

enter image description here

=(COUNTIFS(sheet2_2019!A:A;"2*";sheet2_2019!A:A;SUMPRODUCT(N(LEN(sheet_2019!A:A)=9))))

The first part is easy, to see if the cell starts with "2" but the length I not figuring out how to do it.

Do you know how can I solve this?

Thanks

3
excelcountif
Maybe: "2????????" – JvdV
@JvdV that won't work if the data is actually numbers though and not text. I can't tell from the screenshot though. – BigBen
@BigBen I believe the OP has the values stored as text, by the fact that they stated: The first part is easy, to see if the cell starts with "2" and is using COUNTIFS(sheet2_2019!A:A;"2*" already – Scott Craner
@JvdV please put that as an answer, because in this case, I believe it to be the best. – Scott Craner
@ScottCraner, I'm on the phone so I threw down a very bare answer. – JvdV

3 Answers

3
votes

Instead of the asterisks, you could apply the question mark wildcard as a placeholder for any character making up a string of nine in total.: 2????????

=COUNTIFS(sheet2_2019!A:A,"2????????")
2
votes

If you are working with numbers, perhaps like this:

=COUNTIFS(sheet2_2019!A:A;">="&2*10^8;sheet2_2019!A:A;"<"&3*10^8)

This is based on the assumption that the underlying data is true numbers, and not values stored as text.

2
votes

You cannot use COUNTIFS and use LEN(). When needing to manipulate the data one will need to use an array type formula like SUMPRODUCT:

=SUMPRODUCT((LEFT(sheet2_2019!A2:A4)="2")*(LEN(sheet2_2019!A2:A4)=9))

Being an array like formula, for best performance one must use the data range and not full column references.