COUNTIFS to Exclude Blanks that contain formulas

[zero269]

Hello,

I'm having some trouble with counting cells using two exclusions: "x" or BLANK.

I'm running my test against two tables: One without and one with formulas. The one without formulas produces the correct number (C25), but the one with formulas (G25) is counting the BLANK cells when it shouldn't.

I'm using the following formula for both tests:

=COUNTIFS(tblBooks2B[Quiz],"<990000",tblBooks2B[Avail.],"<>"&"x",tblBooks2B[Avail.],"<>"&"")

And I tried droping that empty space "", but that doesn't change the results.

=COUNTIFS(tblBooks2B[Quiz],"<990000",tblBooks2B[Avail.],"<>"&"x",tblBooks2B[Avail.],"<>")

Here's what my Sample Data looks like:
Left Table has no formulas (C) and the Right Table is using formulas (G)

VBA Testing.xlsm
	A	B	C	D	E	F	G
1	Quiz	Title	Avail.		Quiz	Title	Avail.
2	990001	1-2-3 Peas	x		990001	1-2-3 Peas	x
3	990003	A Best Friend for Woodstock			990003	A Best Friend for Woodstock
4	990004	A Dark, Dark Tale			990004	A Dark, Dark Tale
5	990005	A Gift for Grandpa	hoopla		990005	A Gift for Grandpa	hoopla
6	81719	A Hat for Ivan	lac		81719	A Hat for Ivan	lac
7	990006	A Light in the Attic	x		990006	A Light in the Attic	x
8	990007	A Treasury of Mother Goose	hoopla		990007	A Treasury of Mother Goose	hoopla
9	73447	Alabaster's Song	lac		73447	Alabaster's Song	lac
10	990009	All the World	x		990009	All the World	x
11	990012	Art			990012	Art
12	990013	Bathtime PiggyWiggy	hoopla		990013	Bathtime PiggyWiggy	hoopla
13	167854	Boy and the Ocean	lac		167854	Boy and the Ocean	lac
14	102089	Crippled Lamb	hoopla		102089	Crippled Lamb	hoopla
15	990076	Halloween Is Here, Corduroy!	hoopla		990076	Halloween Is Here, Corduroy!	hoopla
16	163599	Itsy Bitsy Christmas	x		163599	Itsy Bitsy Christmas	x
17	52978	Just in Case You Ever Wonder	lac		52978	Just in Case You Ever Wonder	lac
18	109743	Oak Inside the Acorn			109743	Oak Inside the Acorn
19	990008	Prince Otto: A Romance			990008	Prince Otto: A Romance
20	130775	Story of a Fierce Bad Rabbit	x		130775	Story of a Fierce Bad Rabbit	x
21	147312	Tale of a Naughty Little Rabbit	x		147312	Tale of a Naughty Little Rabbit	x
22	182439	You Are Mine			182439	You Are Mine
23	Total	21	15		Total	21	21
24
25	Available	Exclude "x" & EMPTY only	5		Available	Exclude "x" & EMPTY only	7
26	Not Available	Count "x" only	3		Not Available	Count "x" only	3
27	Unknown	Count <blank> only	2		Unknown	Count <blank> only	2
28	Total		10		Total		12
Books

Cell Formulas
Range	Formula
G2:G22	G2	=IF(ISBLANK(XLOOKUP([@Quiz],tblBooks2A[Quiz],tblBooks2A[Avail.])),"",XLOOKUP([@Quiz],tblBooks2A[Quiz],tblBooks2A[Avail.],""))
B23,F23	B23	=SUBTOTAL(103,[Title])
C23,G23	C23	=SUBTOTAL(103,[Avail.])
C25	C25	=COUNTIFS(tblBooks2A[Quiz],"<990000",tblBooks2A[Avail.],"<>"&"x",tblBooks2A[Avail.],"<>"&"")
C26	C26	=COUNTIFS(tblBooks2A[Quiz],"<990000",tblBooks2A[Avail.],"x")
C27	C27	=COUNTIFS(tblBooks2A[Quiz],"<990000",tblBooks2A[Avail.],"")
C28,G28	C28	=SUM(C25:C27)
G25	G25	=COUNTIFS(tblBooks2B[Quiz],"<990000",tblBooks2B[Avail.],"<>"&"x",tblBooks2B[Avail.],"<>"&"")
G26	G26	=COUNTIFS(tblBooks2B[Quiz],"<990000",tblBooks2B[Avail.],"x")
G27	G27	=COUNTIFS(tblBooks2B[Quiz],"<990000",tblBooks2B[Avail.],"")

Does anyone know how I can do this? I'd like to use this across both table types if possible.
Thank you,

 

[Alex Blakenburg]

Either of these should work:

=COUNTA(FILTER(tblBooks2B[Quiz],(tblBooks2B[Quiz]<990000)*(tblBooks2B[Avail.]<>"x")*(tblBooks2B[Avail.]<>"")))

OR

=SUMPRODUCT((tblBooks2B[Quiz]<990000)*(tblBooks2B[Avail.]<>"x")*(tblBooks2B[Avail.]<>""))

 

[zero269]

Either of these should work:

=COUNTA(FILTER(tblBooks2B[Quiz],(tblBooks2B[Quiz]<990000)*(tblBooks2B[Avail.]<>"x")*(tblBooks2B[Avail.]<>"")))

OR

=SUMPRODUCT((tblBooks2B[Quiz]<990000)*(tblBooks2B[Avail.]<>"x")*(tblBooks2B[Avail.]<>""))

Hi Alex,

Thanks again for your expert help.

I tested them both and they absolutely produce the correct values by excluding the cells with "x" and Blanks.
I think I'll use the SUMPRODUCT. I'll need to dissect it later on to see how each portion of the formula works. It's very interesting.
Thank you,

 

[Alex Blakenburg]

Both the filter function and sumproduct are evaluation <>"" differently to the CountIfs, SumIfs functions.
If you can't figure out how they work come back to me.