Multicriteria error

[babuifav]

I am trying to count how many word chassis from column Failure to Failure 7 was found on 2023. But I have an error #Value. Not sure what I am doing incorrect.

=COUNTIFS(Data[[FAILURE ]:[FAILURE7]],"Chassis",Data[Year],"2023")

=COUNTIFS(Data[Year],B18,Data[[FAILURE]],"Chassis",Data[FAILURE2],"Chassis",Data[FAILURE3],"Chassis",Data[FAILURE4],"Chassis",Data[FAILURE5],"Chassis",Data[FAILURE6],"Chassis",Data[FAILURE7],"Chassis"). Error was 0 and it should be 8.

=SUMPRODUCT(--(Data[[FAILURE ]:[FAILURE7]]="Chassis"),--(Data[Year]=2023)). Error #Value!

Thank you

 

[dermie_72]

can you share the split of data including headings showing the formula and the output, plus expected output.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have the available functions, then try

=LET(f,TOCOL(Data[[FAILURE]:[FAILURE7]]),IFNA(ROWS(FILTER(f,f="Chassis",NA())),0))

 

[babuifav]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have the available functions, then try

=LET(f,TOCOL(Data[[FAILURE]:[FAILURE7]]),IFNA(ROWS(FILTER(f,f="Chassis",NA())),0))[/CODE
[/QUOTE]

can you share the split of data including headings showing the formula and the output, plus expected output.

 

[babuifav]

Welcome to the MrExcel board!

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have the available functions, then try

=LET(f,TOCOL(Data[[FAILURE]:[FAILURE7]]),IFNA(ROWS(FILTER(f,f="Chassis",NA())),0))

Thank you Peter, I updated my account for 365 version.
I wasn't able to replicate with the function LET

 

[RoryA]

Not sure what I am doing incorrect

COUNTIFS requires all ranges used to be the same size and shape. Your first range has more columns than the second, so it won't work. You can use SUMPRODUCT:

=SUMPRODUCT((Data[[FAILURE ]:[FAILURE7]]="Chassis")*(Data[Year]=2023))

 

[babuifav]

COUNTIFS requires all ranges used to be the same size and shape. Your first range has more columns than the second, so it won't work. You can use SUMPRODUCT:

=SUMPRODUCT((Data[[FAILURE ]:[FAILURE7]]="Chassis")*(Data[Year]=2023))

Yeahhhhh... it worked thank you.

 

[Peter_SSs]

I wasn't able to replicate with the function LET

Probably because I made a couple of mistakes.
I missed that it seems your "FAILURE " column has a space character at the end & I forgot about the year check.

Here is my small dummy sample data with corrections for those two errors. I've manually coloured the 4 cells that the formula has counted.
(Probably simpler to stick with the SUMPRODUCT formula though )

babuifav.xlsm
	A	B	C	D	E	F	G
1	Year	FAILURE	FAILURE1	FAILURE7	FAILURE8
2	2023	Chassis	Body		Chassis		4
3	2022		Chassis	Other	Other
4	2021	Wheels	Chassis		Chassis
5	2023	Chassis	Chassis	Chassis	Chassis
6
7
Sheet1

Cell Formulas
Range	Formula
G2	G2	=LET(f,Data[[FAILURE ]:[FAILURE7]],c,TOCOL(IF(Data[Year]=2023,f,""),1),IFNA(ROWS(FILTER(c,c="Chassis",NA())),0))