# "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

1. ## "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Hi

Can someone tell me what is wrong with the COUNTIF and why SUMPRODUCT picks up those 2 rows?

edit: I meant rows 10 and 31 (not 32)

Note: entries in col A are entered as text.

2. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Hi,

This is confusing. Why do you mention rows 10 & 31 when column C doesn't have a number anyway

Please confirm what results you do expect.

3. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Try this...
=COUNTIF(A10:A31,"302*")

4. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

THis gives you 8...
=SUMPRODUCT(--(C10:C31>0),--(LEFT(A10:A31,3)<>"302"),--(LEFT(A10:A31,3)<>"306"),--(LEFT(A10:A31,1)<>"2"),--(LEFT(A10:A31,1)<>"6"))

5. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Try selecting C10 and C31 and hitting the delete key. SUMPRODUCT will return 6. There is text in those cells and ("text" >0) = TRUE.

6. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by FDibbins
Try this...
=COUNTIF(A10:A31,"302*")
Does not work on office 2002

7. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by FDibbins
THis gives you 8...
=SUMPRODUCT(--(C10:C31>0),--(LEFT(A10:A31,3)<>"302"),--(LEFT(A10:A31,3)<>"306"),--(LEFT(A10:A31,1)<>"2"),--(LEFT(A10:A31,1)<>"6"))
Yes, but 8 is wrong!

8. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by FlameRetired
Try selecting C10 and C31 and hitting the delete key. SUMPRODUCT will return 6. There is text in those cells and ("text" >0) = TRUE.
Bummer! text should be >"" not >0 !!! ( I will Complain to MS !!!)

I d like to exclude them somehow without removing them...

9. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by drgkt
Does not work on office 2002

10. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

As far as why the COUNTIF does not work, it remains a mystery...

11. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by drgkt
Yes, but 8 is wrong!
I dont have the summary now (deleted it), but I can make it again - it showed that 8 was correct. My test summery was a bunch of helper columns that progressively applied your criteria

Why is 8 wrong and what do you think it should be?

12. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by FDibbins
I dont have the summary now (deleted it), but I can make it again - it showed that 8 was correct. My test summery was a bunch of helper columns that progressively applied your criteria

Why is 8 wrong and what do you think it should be?
It should be 6, just count them on the picture.

13. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

OK, I did that yesterday, memory must be failing lol. Try this...

=SUMPRODUCT(--ISNUMBER(C10:C31),--(C10:C31>0),--(LEFT(A10:A31,3)<>"302"),--(LEFT(A10:A31,3)<>"306"),--(LEFT(A10:A31,1)<>"2"),--(LEFT(A10:A31,1)<>"6"))

It would be simpler if you excluded rows 10 and 31

14. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

ok. That did it! Thanks.

Can you tell me why the COUNTIF returns 0?

15. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

to use COUNTIF()...
=COUNTIF(A10:A31,"302*")

See post 6

17. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

OK, unfamiliar with 2002, but I have a feeling that function came out in later versions of excel

18. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Withdrawn by FR. Didn't read previous post #13.

19. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by drgkt
ok. That did it! Thanks.

Can you tell me why the COUNTIF returns 0?
If you are referring to =COUNTIF((A10:A31);(LEFT((A10:A31);3))="302") it is because there are no lone "302" in A10:A31.

20. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

FR does does 2002 have COUNTIF()

21. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by drgkt
Does not work on office 2002
What means this? COUNTIF did work in 2002.

22. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Izandol, does 2003 have countif()? (I cant remember)

23. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Double posted

24. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by FDibbins
FR does does 2002 have COUNTIF()
I'm sure it does. My introduction to COUNTIF was 1996-1998. I forget what version of Office we had at that time.

25. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by FlameRetired
If you are referring to =COUNTIF((A10:A31);(LEFT((A10:A31);3))="302") it is because there are no lone "302" in A10:A31.
But we did not specify "lone" "302" we specified the left 3 characters of the entry. Am I wrong?

Yes COUNTIF exists as a function in 2002.

26. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by Izandol
What means this? COUNTIF did work in 2002.
COUNTIF works but not with wildcards in 2002. Read the exact quoted post.

27. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by drgkt
But we did not specify "lone" "302" we specified the left 3 characters of the entry. Am I wrong?

Yes COUNTIF exists as a function in 2002.
No. You are not wrong about that. What I was trying to draw attention to is that COUNTIF will go searching for the string "302" in a range which does not include a "302" by itself. If you cannot use a wildcard AND you wish to use COUNTIF a rather awkward array formula approach is going to have to be applied to the criteria argument. This should work unless 2002 also has further limitations on the criteria argument. It must be array entered.

=SUM(IF((COUNTIF(A10:A31,IF(LEFT(A10:A31,3)="302",A10:A31))),1))

Try applying Evaluate formula to both your original formula and this one. You will be able to see the difference in action.

And BTW.
COUNTIF works but not with wildcards in 2002. Read the exact quoted post.
I am sure we all read the exact quoted post. It was not clear ... at least not to me ... which part was not working. Until now I was never aware of a version of COUNTIF that didn't accept wildcards so the thought never seriously occurred to me. I am aware now so thank you for alerting us to that.

28. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

If you see A2, I can get the result via SUMPRODUCT.

I was just beating my head why COUNTIF does not work. I still do not get it. The formula is correct logically and the syntax is correct...

29. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

I still do not get it. The formula is correct logically and the syntax is correct...
Yes. That is exactly right. That is part of the problem. In fact believe it or not it is so exactly correct that COUNTIF returns 0.

Would you like me to guide you through the steps in both versions of the formula so you can see why?

It will require your participation and use of the F9 function key. If you are not familiar with what can be done with F9 in each of the steps you can think of it as an alternative to the Evaluate Formula feature. I use it all the time.

30. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

FR, Fx is also a really good tool, although also not sure if 2002 has that either

31. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by FDibbins
FR, Fx is also a really good tool, although also not sure if 2002 has that either
Yes. Ford I started to include that, and then it occurred to me that it might not be. Additionally when I tried to run the original COUNTIF through Fx it stubbornly returned #VALUE error on the LEFT(A10:A31,3) part. Changing the range to A11:A30 did the same. Geeesh!

Thank you for staying with us on this one. I for one am learning a lot.

32. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by drgkt
COUNTIF works but not with wildcards in 2002. Read the exact quoted post.
Where did you say this before? Only said it did not work. COUNTIF did always support wildcards but wildcard will not work with number values (your 302xxxxx of course is not number): https://support.microsoft.com/en-gb/kb/214153

33. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by Izandol
Where did you say this before? Only said it did not work. COUNTIF did always support wildcards but wildcard will not work with number values (your 302xxxxx of course is not number): https://support.microsoft.com/en-gb/kb/214153
Sorry for the misunderstanding.
In post #6 I stated "=COUNTIF(A10:A31,"302*") does not work in office 2002". I meant the wildcard, not the function.
I also mentioned that the entries are entered as text. I can now see why.

34. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

Originally Posted by FlameRetired
Yes. That is exactly right. That is part of the problem. In fact believe it or not it is so exactly correct that COUNTIF returns 0.

Would you like me to guide you through the steps in both versions of the formula so you can see why?

It will require your participation and use of the F9 function key. If you are not familiar with what can be done with F9 in each of the steps you can think of it as an alternative to the Evaluate Formula feature. I use it all the time.
I am not familiar with the F9, and trying to use evaluate did not sink either!

35. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

@ drgkt,

This will take me some time to organize my thoughts and do all the necessary copy / paste. I have some competing tasks to complete as well.

Glad to see you coming back for this one.

36. ## Re: "Countif" with "left" error, "Sumproduct" picking up not qualifying rows

To start if you were not aware of it in arrays the semi-colons are considered row separators. Commas are column separators. There are no column separators in the following arrays to be concerned with.

All highlighting and use of the F9 key is carried out in the formula bar.

Working this formula:

=COUNTIF(A10:A31
,LEFT((A10:A31),3)="302")

First select only the A10:A31 in the first or range argument and press the F9 function key.

We see this, the range argument, as an array.

=COUNTIF({"-";"11770";"11880";"11880";"11880";"13830";"13830";"13830";"14584";"14584";"14584";"30210xxxxxxx";"30210xxxxxxx";"210xxxxxxx";"210xxxxxxx";"306xxxxxxxxx";"306xxxxxxxxx";"6xxxxxxxxx";"6xxxxxxxxx";"801xxxxxxxxx";"801xxxxxxxxx";"-"}
,LEFT((A10:A31),3)
="302")

Next while still in edit mode select the LEFT((A10:A31),3)
part and hit F9 again.

We see:

=COUNTIF({"-";"11770";"11880";"11880";"11880";"13830";"13830";"13830";"14584";"14584";"14584";"30210xxxxxxx";"30210xxxxxxx";"210xxxxxxx";"210xxxxxxx";"306xxxxxxxxx";"306xxxxxxxxx";"6xxxxxxxxx";"6xxxxxxxxx";"801xxxxxxxxx";"801xxxxxxxxx";"-"}, {"-";"117";"118";"118";"118";"138";"138";"138";"145";"145";"145";"302";"302";"210";"210";"306";"306";"6xx";"6xx";"801";"801";"-"}="302")

All of the 3 left most characters from A10:A31 are in the second argument or criteria array. But the argument is not complete. We need to also include the =”302” part. Extend the last selection to include that as well and hit the F9 key.

We see this.

=COUNTIF({"-";"11770";"11880";"11880";"11880";"13830";"13830";"13830";"14584";"14584";"14584";"30210xxxxxxx";"30210xxxxxxx";"210xxxxxxx";"210xxxxxxx";"306xxxxxxxxx";"306xxxxxxxxx";"6xxxxxxxxx";"6xxxxxxxxx";"801xxxxxxxxx";"801xxxxxxxxx";"-"}, {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

At this stage we see that COUNTIF supplied with that output of the criteria argument is told to find the literal strings TRUE and FALSE (and that is how COUNTIF interprets) within the first argument A10:A31 seen in the array

{"-";"11770";"11880";"11880";"11880";"13830";"13830";"13830";"14584";"14584";"14584";"30210xxxxxxx";"30210xxxxxxx";"210xxxxxxx";"210xxxxxxx";"306xxxxxxxxx";"306xxxxxxxxx";"6xxxxxxxxx";"6xxxxxxxxx";"801xxxxxxxxx";"801xxxxxxxxx";"-"}

It finds none and returns 0.

We can usually F9 our way through any formula in this manner. Should Evaluate Formula feature be available that also works and is much faster than the tedious F9 method. The view port is quite small though. There are other differences. Know that sometimes the F9 method can produce misleading results. This is comparatively rare and I won’t elaborate here.

You’ve chosen SUMPRODUCT. In my opinion that is the wise choice in this case.

Did this help?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1