How to shorten Too Long Countif formula

[putraguevara]

Hi all, this is my first time posting and glad i finally found this forum. I'm using Ms. Excel 2016

I have a question on how to reduce too long countif formula, let's say i have 84 criteria that i want to look on one data list in excel. when i typed the formula excel returns with you can't use more than 8192 characters in an Microsoft Excel Formula. I attached the the formula that i want to use. Glad if anyone could help.

Please Login or Register  to view this content.

Basically i just want to know the value of one criteria after being subtracted with 84 criteria. your replies would be awesome. Thanks

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[FDibbins]

Welcome to the forum

Looks to me like each countifs is essentially the same, the only difference being "1 (W)"
Have you considered something like
"* (W)"

[putraguevara]

is it possible to add *, if i only have certain amount of 1 (W), let's say 1 (W) until 12 (W). gonna try it first. Thanks for the reply though

[putraguevara]

it doesn't work, though. wish i could attached the excel

[putraguevara]

hi glenn, thanks for the reply, but i still couldn't attached my excel file. it says have to wait after posting few thread

[Glenn Kennedy]

Not so. Try again. You cannot post www links, but you can post files.

[putraguevara]

Hi, Glenn thanks so much for the guidance. here is the excel

Please Login or Register  to view this content.

[Glenn Kennedy]

More explanation please... To count Special category in Widhya Asih is easy:

=COUNTIFS(B:B,"Widhya Asih",C:C,"Special")

but you need to explain the connection between columns I to P and school grade....

[jason.b75]

Does this give the expected result?

=COUNTIFS(B4:B3615,"Widhya Asih",F4:F3615,"<>*(*)")

edit:- Missed the bit about "Special", maybe this one instead?

=COUNTIFS(B4:B3615,"Widhya Asih",C4:C3615,"Special",F4:F3615,"<>*(*)")

[putraguevara]

As you can see, on the excel file there are two data. The first one is the data list and the second one is the category that i want to look for.

In this problem, i want to find out how many students that are in widhya asih, with special category that its school grade doesnt belong to the "category that i want to look for" list.

Lets say it will count how many students from widhya asih that belong in special category with the school grade of 7 or any other number as long as it doesn't listed on the "category that i want to look for"

[Glenn Kennedy]

So this counts Place, Category and (maybe??) Grade

In Q5, copied down:
=COUNTIFS(B:B,"Widhya Asih",C:C,"Special",F:F,I5)

If this is NOT correct, please supply manually calculated results.

[putraguevara]

So this counts Place, Category and (maybe??) Grade

In Q5, copied down:
=COUNTIFS(B:B,"Widhya Asih",C:C,"Special",F:F,I5)

If this is NOT correct, please supply manually calculated results.

Please Login or Register  to view this content.

I enclose the sum amount i want to look for. The countifs should display the sum of filtered result.

But somehow it didn't

[jason.b75]

Why is row 1471 not counted? It is not in the table to exclude?

To exclude

=COUNTIFS(B4:B3615,"Widhya Asih",C4:C3615,"Special",F4:F3615,"<>*",F4:F3615,"<>")

to include

=COUNTIFS(B4:B3615,"Widhya Asih",C4:C3615,"Special",F4:F3615,"<>*(*)",F4:F3615,"<>")

[Glenn Kennedy]

OK! My previous formula gave that result, but split by Grade (so... 0 class 1, 4 calss 11, 6 class 12. For an overall total, use this array formula:

=COUNT(IF($B$5:$B$4000="Widhya Asih",IF($C$5:$C$4000="Special",IF(ISERROR(MATCH($F$5:$F$4000,$I$5:$I$16,0)),"",$F$5:$F$4000))))

It's in R6.



Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[putraguevara]

hi, i just opened the reply, it seems work for me. Would you explain me how the formula works? just wondering about the flow. Thanks for your help though

[jason.b75]

So to simplify it, you want to count how many Rows contian "Widhya Asih", "Special" and a number in column F (no text or blanks).

Which is exactly what my much simpler suggestion (which you chose to ignore) does.

If you want an answer, just accept any one given to you, if you want a solution, then look at all of the answers given to you!

[Glenn Kennedy]

Great!!

=COUNT(IF($B$5:$B$4000="Widhya Asih",IF($C$5:$C$4000="Special",IF(ISERROR(MATCH($F$5:$F$4000,$I$5:$I$16,0)),"",$F$5:$F$4000))))

Red: Ff this is true, return TRUE/FALSE
Orange: AND this is true
Green: If the value in column F is NOT found in I5 to I16, return a blank, otherwise
Blue: return the value in column F..

Black: If the conditions are both true AND a number in F has been returned, count the cells that meet those conditions.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[putraguevara]

hi, dude. happy new year 2019, wish you be blessed year ahead.

Nope, i am not ignoring your suggestion, it's been a big help for me. i had alternatives in clearing my work. Thanks lot for the trick. I am just a noob, sorry if my words were offend you.

[putraguevara]

Why is row 1471 not counted? It is not in the table to exclude?

To exclude

=COUNTIFS(B4:B3615,"Widhya Asih",C4:C3615,"Special",F4:F3615,"<>*",F4:F3615,"<>")

to include

=COUNTIFS(B4:B3615,"Widhya Asih",C4:C3615,"Special",F4:F3615,"<>*(*)",F4:F3615,"<>")

Hi jas, i just figured out that the "<>*(*) characters were awesome, thanks for enlightening me with this, never know it before.

Thanks lot

[jason.b75]

You're welcome!

Sorry if my previous response was a bit harsh, it can be quite common for new members to take a discourteous approach to the help that they are given on the forum, which was how was looking at you acknowledging Glenn's replies but not mine.

Even the most experienced people on this forum learn new things from the answers provided here. Even answers that are 'wrong' for the question asked at the time can contain useful things that you might not have known previously.

[FlameRetired]

...
Even the most experienced people on this forum learn new things from the answers provided here. Even answers that are 'wrong' for the question asked at the time can contain useful things that you might not have known previously.

Amen to that!