Cell counting formula in Excel 2010

2
votes

I need to count the cells in column A with the word "Alex White", if in the same row in the cell in column D the text starts with "2012.02"? What could be the Excel 2010 formula for that?

None of the formulas below worked for me:

=SUM(IF(A2:A5000="Alex";1;0)+IF(D2:D5000="2012.02*";1;0))
=SUM(IF(AND((A2:A5000="Alex");(LEFT(D3;7)="2012.02"));1;0))
=SUMPRODUCT((D2:D5000="2012.02*");(A2:A5000="Alex"))

I'll be looking forward to your answer!

3
excelcountexcel-formula

3 Answers

3
votes

This one works:

=SUMPRODUCT(--(LEFT(D2:D5000;7)="2012.02");--(A2:A5000="Alex"))

(If you want to match "Alex White", you obviously have to use this string instead). I assume that "2012.02" really is a string. If it is a date, you'd have to do something like this:

=SUMPRODUCT(--(D2:D5000>=DATE(2012;2;1));--(D2:D5000<=DATE(2012;3;0));--(A2:A5000="Alex"))
1
votes

Enter the following as an array formula by pressing Ctrl+Shift+Enter. 

=SUM(IF(A2:A5000="Alex White",IF(LEFT(D2:D5000,7)="2012.02",1,0),0))
1
votes

The array formulas of stephan and Excelll will work but I would like to point out that you could use COUNTIFS:

=COUNTIFS(A2:A5000,"Alex White",D2:D5000,"2012.02*")

or the french version (one never knows if needed):

=NB.SI.ENS(A2:A5000;"Alex White";D2:D5000;"2012.02*")