Count blank Multiple condition

[mira2020]

hi all,

I need help to write count formula. I want to count for each Fruit type, the number of cell in column B is 0 or blank .
there is formula in column B, so if it blank, means the formula return a blank value . Count still return 1 if the value is blank because the formula is there, but i want it return 0
Thanks.

 

[Alex Blakenburg]

I am a little confused as to what you are trying to count.
Since the Countif(s) only have an issue when you are trying to exclude just the blanks I have assumed you want to count everything except blanks.

20231120 Count ignore blanks mira2020.xlsx
	A	B	C	D	E	F	G
1	Fruit	Price				SumProd not blank	CountIfs not blank
2	Apple	1			Apple	3	3
3	Apple	0			Orange	1	1
4	Orange	3			Banana	2	2
5	Apple	4
6	Orange
7	Banana	0
8	Banana	0
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT(($A$2:$A$8=E2)*($B$2:$B$8<>""))
G2:G4	G2	=COUNTIFS($A$2:$A$8,E2,$B$2:$B$8,">=0")
B6	B6	=""

 

[Georgiboy]

You could adjust the formula that creates the values in column B to return a 0 instead of a blank or use one of the methods below:

Book1
	A	B	C	D	E	F	G	H
1	Fruit	Price
2	Apple	1				Apple	1	1
3	Apple	0				Orange	1	1
4	Orange	3				Banana	2	2
5	Apple	4
6	Orange
7	Banana	0
8	Banana	0
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=COUNTIFS($A$2:$A$8,F2,$B$2:$B$8,"<1")+COUNTIFS($A$2:$A$8,F2,$B$2:$B$8,"")
H2:H4	H2	=SUMPRODUCT(($A$2:$A$8=F2)*(($B$2:$B$8<1)+($B$2:$B$8="")))
B2	B2	=1
B3,B7:B8	B3	=0
B4	B4	=3
B5	B5	=4
B6	B6	=""

 

[Alex Blakenburg]

@Georgiboy, the results the OP has given are at odds with the statement

Count still return 1 if the value is blank because the formula is there, but i want it return 0

And just counting the 0s and excluding the blanks is easy
This will do that.

=COUNTIFS($A$2:$A$8,E2,$B$2:$B$8,0)

So I suspect that the example is flawed.
Hopefully the OP will come back and clarify.

 

[Georgiboy]

I mainly looked at the below part of the OP:

I want to count for each Fruit type, the number of cell in column B is 0 or blank

The countifs function will not count "" as part of "<1" for example, so i have assumed the TS wants to count 0's and "" together as one.