how do I only count a value in a range that starts with "900"

[Wildboer]

I have a range of invoice numbers which changes to credit note numbers, in the same column. I need to only count the credit notes.
The invoice numbers start with 120.. numbers, and the credit notes with 900... numbers.
Can someone please help me?

[NBVC]

Try possibly:

=COUNTIF(A:A,"120*")

or if the 120 is a cell, like D1, then =COUNTIF(A:A,D1&"*")

[Wildboer]

Thank you, but it's giving me a 0 value? What could be wrong?

[NBVC]

Probably column A are "real numbers" instead of text strings...

Try instead:

=SUMPRODUCT(--(LEFT(A2:A100&"",3)="120"))

adjust range to suit.

[Wildboer]

I think i am getting there, but now it's giving me a "TRUE" Value?

[NBVC]

What is the exact formula you are using?

[daddylonglegs]

The invoice numbers start with 120.. numbers, and the credit notes with 900... numbers.

Is there a fixed number of digits? Assuming 6 digit numbers (adjust as required) you could use COUNTIFS like this

=COUNTIFS(A:A,">=120000",A:A,"<=120999")

[Wildboer]

=SUMPRODUCT(--(LEFT(C2:C999,3)="900"))
I used this basic formula to count amount of invoices: =COUNTA(B2:B1000)

[NBVC]

You are missing a little piece....

=SUMPRODUCT(--(LEFT(C2:C999&"",3)="900"))

also, have a look at daddylonglegs' suggestion....

[Wildboer]

Still getting either true or false values?
I also tried this : =IF(C2>"90000000",COUNTA(C2:C999),"no credit notes"), and I get a "no credit notes value")
900000599 - example of credit note nr
12743448 - example of invoice number

[NBVC]

I am not sure how my Sumproduct formula would give TRUE or FALSE result.. it would be numerical or an error!

with your version, you would need to remove the quotes around the number... but that will count all numbers in C2:C999 if C2 is bigger, not if all are bigger...

you can possibly try:

=COUNTIF(C2:C999,">=90000000") to count all number greater than 90000000

[Wildboer]

Thank you for your effort. Still getting "false". I think i'll create another column, to display wether or not the number is above 900..., and then count the true values?

---------- Post added at 04:18 PM ---------- Previous post was at 04:13 PM ----------

I realized that although it's giving me a false value, it gives the right answer where I use the cell reference in my formulas in the other sheets!

[NBVC]

I wonder how you have the cell showing False formatted?