help me with COUNTA formula

[rexcel3]

i have a table with information.

im using filter formula to show some data.

when using Friday and Exclude Dylan there is empty information in filter which is correct but counta formula is counting this as 1. why?

im using swedish excel so we use ; in formulas.

Filter:

excel.xlsx
	B	C	D	E	F	G	H
2	Choose day	Friday	Exclude	Dylan		Amount	1
3	Leverantör	Konsult	Att göra	Dag 1	Dag 2	E-post	Status
4
Veckorutiner

Cell Formulas
Range	Formula
H2	H2	=COUNTA(B4:B1000)
B4	B4	=IF(FILTER(attgöra_tabell6, ((attgöra_tabell6[Dag 1]=Veckorutiner!C2) + (attgöra_tabell6[Dag 2]=Veckorutiner!C2)) * (attgöra_tabell6[Konsult]<>Veckorutiner!E2), "")="", "", FILTER(attgöra_tabell6, ((attgöra_tabell6[Dag 1]=Veckorutiner!C2) + (attgöra_tabell6[Dag 2]=Veckorutiner!C2)) * (attgöra_tabell6[Konsult]<>Veckorutiner!E2), ""))

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=Dagar
E2	List	=Konsulter

Table:

excel.xlsx
	B	C	D	E	F	G
3	Leverantör	Konsult	Att göra	Dag 1	Dag 2	E-post
4	x	Jack	sfs	Monday	Wednesday	aesvgv
5	xx	Black	af	Tuesday		gfvadgfdv
6	xxx	Michael	adfgg	Thursday		agdfvgavf
7	a	Dylan	afgagfa	Monday	Thursday	agvfagvf
8	aa	Black	adfgadg	Tuesday		agvfagvf
9	aaa	Black	afgadg	Tuesday		etynjujent
10	s	Dylan	sfghjfsh	Wednesday		t,oitli,
11	ss	Dylan	etyheyth	Monday		y5wwy5
12	sss	Homer	sbhshb	Monday	Tuesday	tq45qt
13	d	Black	shbgshg	Tuesday		eiuj7i7jre
14	dd	Jack	shbghsgb	Monday	Wednesday	8irok8rij
15	ddd	Dylan	sbhshbg	Friday		y5tw5y
16	f	Jack	sbghsgb	Monday	Wednesday	tqqt
17	ff	Michael	shvshb	Thursday		ue75jhu7e
18	fff	Dylan	sbhgshbg	Thursday	Wednesday	uh65u6h
19	g	Rima	sbhshbg	Wednesday		y6w4hwy64
20	gg	Homer	sbhshbg	Monday	Tuesday	y56g4wyw5
21	ggg	Dylan	ayevyva	Monday	Thursday	ywg5yw
22	h	Dylan	avy5eave	Thursday		wy5g
23	hh	Dylan	avey5vaeyh	Friday		wyg5
Databas

 

[StephenCrump]

Your FILTER formula is returning a blank string, which COUNTA will count.

In simple terms:

	A	B
1		1
Sheet1

Cell Formulas
Range	Formula
A1	A1	=""
B1	B1	=COUNTA(A:A)

 

[coulombevin]

Hi rexcel3,

Like StephenCrump wrote, your filter function, if empty, will return "" which is considered an empty element, but still an element. Your COUNTA will then count 1 element even if the element is "".

To prevent that, i would do something like: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

=IF(B4 = "", 0, COUNTA(B4:B1000))

Bests regards,

Vincent

 

[Alex Blakenburg]

In Excel 2021, you should be able to use this in H2

=SUM(--(INDEX(B4#,0,1)<>""))

Where B4# is the SPILL range and the Index function gets just the first column of the SPILL range.

Note: Like CountA the CountIfs category of formulas will include "" even if you use "<>"

 

[rexcel3]

Hi rexcel3,

Like StephenCrump wrote, your filter function, if empty, will return "" which is considered an empty element, but still an element. Your COUNTA will then count 1 element even if the element is "".

To prevent that, i would do something like: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

=IF(B4 = "", 0, COUNTA(B4:B1000))

Bests regards,

Vincent

thank u, this solved my issue.

=OM(B4 = ""; 0;ANTALV(B4:B1000))