Count a row with two conditions

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hey,

I have been working on this problem for some time and went through various help files as well as research on google.

I have something like this

ID Date

D1 1/1
D2 1/1
N1 1/1
D3 1/2
D4 1/2
N5 1/1
N3 1/2
D2 1/3
D2 1/3



D1 1/1
D2 1/1
D1 1/1
D3 1/2
D4 1/2
D5 1/1
D3 1/2
D2 1/3
D2 1/3

This is a short example, but I have thousands of entries that show an ID and a Date in the sam row. What I need to do is make a conditional statment that can count how many entires D1 has on 1/1, N1 has on 1/1 etc. Also I would like to seperate the D's and N's as shown bellow.

Date D's N's
1/1
1/2
1/3
1/4
1/5


If anyone could help that would be great because I am stuck. I have tried countif but that can only use 1 condition and everythin else I have tried will not search through a list of ID's that I have for N or D.

Thanks in advance.

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
try this formula

=SUMPRODUCT(($A$2:$A$10000="D1")*($B$2:$B$10000="1/1/2009"+0))

If the no. of rows is not more than 10,000.
the actual data starts from second row with row 1 having headings.

for separating Ds and Ns just sort the data.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month