Count a row with two conditions [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
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 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!