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.
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.
Last edited by drgkt; 01-09-2016 at 06:39 PM.
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.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
Try this...
=COUNTIF(A10:A31,"302*")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
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"))
Try selecting C10 and C31 and hitting the delete key. SUMPRODUCT will return 6. There is text in those cells and ("text" >0) = TRUE.
Dave
As far as why the COUNTIF does not work, it remains a mystery...
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
ok. That did it! Thanks.
Can you tell me why the COUNTIF returns 0?
to use COUNTIF()...
=COUNTIF(A10:A31,"302*")
See post 6
OK, unfamiliar with 2002, but I have a feeling that function came out in later versions of excel
Withdrawn by FR. Didn't read previous post #13.
Last edited by FlameRetired; 01-10-2016 at 05:43 PM.
FR does does 2002 have COUNTIF()
- 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
Izandol, does 2003 have countif()? (I cant remember)
Double posted
Last edited by FlameRetired; 01-10-2016 at 06:35 PM.
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.
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.COUNTIF works but not with wildcards in 2002. Read the exact quoted post.
Last edited by FlameRetired; 01-10-2016 at 07:12 PM.
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...
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.I still do not get it. The formula is correct logically and the syntax is correct...
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.
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.
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
Last edited by drgkt; 01-12-2016 at 06:29 AM.
@ 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.
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?
Last edited by FlameRetired; 01-13-2016 at 02:32 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks