countifs question

[backsidedriver]

I am struggling with countifs. Perhaps I am not using it properly. Here is my scenario.

I want to count cells only if column A = "Applications", column B = "Yes", and column C = column D for each row in a range. Here is my example data

A	B	C	D
Applications	Yes	Web	Web
Applications	No	Windows Client	Web
Database	Yes	Oracle	Oracle
Database	Yes	Oracle	Oracle
Server	No	Linux	Linux
Server	No	Linux	Windows
Applications	Yes	Web	Windows Client

With the above data I expect the count to be equal "1" since there is only one row where all the critera matches. However, I seem to get different answers. I have used the following:

=countifs(A:A,"Applications",B:B,"Yes",C:C,D:D)
and
=countifs(A:A,"Applications",B:B,"Yes",C:C,"="&D:D)

Should this work or am I using the wrong formula?

Thanks in advance for your advice.

 

[Peter_SSs]

Welcome to the MrExcel board!

Here are a couple of options, depending on which of your Excel versions it needs to work in. You can use larger ranges than I have but I strongly recommend not using whole column references.

23 03 01.xlsm
	A	B	C	D	E	F
1	Applications	Yes	Web	Web		1
2	Applications	No	Windows Client	Web		1
3	Database	Yes	Oracle	Oracle
4	Database	Yes	Oracle	Oracle
5	Server	No	Linux	Linux
6	Server	No	Linux	Windows
7	Applications	Yes	Web	Windows Client
8
9
10
Count

Cell Formulas
Range	Formula
F1	F1	=IFNA(ROWS(FILTER(A1:A10,(A1:A10="Applications")*(B1:B10="yes")*(C1:C10=D1:D10),NA())),0)
F2	F2	=SUMPRODUCT(--(A1:A10="Applications"),--(B1:B10="Yes"),--(C1:C10=D1:D10))

 

[backsidedriver]

Welcome to the MrExcel board!

Here are a couple of options, depending on which of your Excel versions it needs to work in. You can use larger ranges than I have but I strongly recommend not using whole column references.

23 03 01.xlsm
	A	B	C	D	E	F
1	Applications	Yes	Web	Web		1
2	Applications	No	Windows Client	Web		1
3	Database	Yes	Oracle	Oracle
4	Database	Yes	Oracle	Oracle
5	Server	No	Linux	Linux
6	Server	No	Linux	Windows
7	Applications	Yes	Web	Windows Client
8
9
10
Count

Cell Formulas
Range	Formula
F1	F1	=IFNA(ROWS(FILTER(A1:A10,(A1:A10="Applications")*(B1:B10="yes")*(C1:C10=D1:D10),NA())),0)
F2	F2	=SUMPRODUCT(--(A1:A10="Applications"),--(B1:B10="Yes"),--(C1:C10=D1:D10))

Thank you for the reply. The first option with, my version of excel (2016), does not support the "FILTER" option. However the second option appears to work except the count is short by 1 in one case but an exact match in another. It is close enough for may needs. Thank you again.

 

[Joe4]

Thank you for the reply. The first option with, my version of excel (2016), does not support the "FILTER" option.

You should update your profile to accurately display the version on Excel you have, so solutions can be tailored to your version.
That is what that field is for, it lets helpers know what version of Excel you are using so they can be sure to come up with solutions that will work for you on your current version of Excel.

 

[kvsrinivasamurthy]

Try

=sumproduct((A:A="Applications)*(B:B="Yes")*(C:C=D=D))

 

[Joe4]

Try

=sumproduct((A:A="Applications)*(B:B="Yes")*(C:C=D=D))

You got a few typos/errors in that formula.
Please try it out and confirm it works before posting it.

 

[DanteAmor]

However the second option appears to work except the count is short by 1 in one case but an exact match in another. It is close enough for may needs.

I don't understand what is the problem with Peter's formula.

The formula works for your requirement:

With the above data I expect the count to be equal "1" since there is only one row where all the critera matches.

I take this opportunity to give you the correction of post #3 and another option with a array formula, to show how it works with the Count function:

Dante Amor
	A	B	C	D	E	F
1	Applications	Yes	Web	Web		2
2	Applications	No	Windows Client	Web		2
3	Database	Yes	Oracle	Oracle		2
4	Database	Yes	Oracle	Oracle
5	Server	No	Linux	Linux
6	Server	No	Linux	Windows
7	Applications	Yes	Windows Client	Windows Client
Hoja5

Cell Formulas
Range	Formula
F1	F1	=SUMPRODUCT(--(A1:A10="Applications"),--(B1:B10="Yes"),--(C1:C10=D1:D10))
F2	F2	=SUMPRODUCT((A1:A10="Applications")*(B1:B10="Yes")*(C1:C10=D1:D10))
F3	F3	=COUNT(IF((A1:A10="Applications")*(B1:B10="Yes")*(C1:C10=D1:D10),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

except the count is short by 1 in one case

Quite likely the problem is that the data contains one or more stray leading or trailing spaces (or could be multiple consecutive internal spaces).
For example, with the data below, visually it looks like the result should be 2 (rows 1 and 7) but the formula I gave above (cell F2) is only returning 1 and that is because in this example, cell D1 has a space character after the word Web and C1 does not.

A formula like I have in F3 below might help identify if the issue does relate to stray space characters.

23 03 01.xlsm
	A	B	C	D	E	F
1	Applications	Yes	Web	Web
2	Applications	No	Windows Client	Web		1
3	Database	Yes	Oracle	Oracle		2
4	Database	Yes	Oracle	Oracle
5	Server	No	Linux	Linux
6	Server	No	Linux	Windows
7	Applications	Yes	Windows Client	Windows Client
8
9
10
Count

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(--(A1:A10="Applications"),--(B1:B10="Yes"),--(C1:C10=D1:D10))
F3	F3	=SUMPRODUCT(--(TRIM(A1:A10)="Applications"),--(TRIM(B1:B10)="Yes"),--(TRIM(C1:C10)=TRIM(D1:D10)))

And yes, please fix your profile as suggested above by @Joe4

 

[kvsrinivasamurthy]

Corrected formula

=SUMPRODUCT((A:A="Applications")*(B:B="Yes")*(C:C=D:D))

 

[DanteAmor]

However the second option appears to work except the count is short by 1 in one case but an exact match in another

If you have problems with your data, as Peter suggests, and you don't have the Unique function, you can obtain a pivot table of each column and thus identify which texts you have problems, see the following example, the ones marked in yellow should be values Unique, but appear more than once. In this way you will be able to identify in which texts you have problems, correct them and use the formulas. Since the best practice is to have a clean database.