SUMIF where the IF is a variable date from data in a column HOW?

[oliverkat]

Aby SUPER USERS out there?

I got a large no of data that are sorted by date, I want to make a formula
that can count the occurencies of the numbers 1, 2 3 etc. in column C and
show the result in column D, E F etc.

The result in D comes from a formula:
=IF(B2=B1,0,SUMPRODUCT((($A$2:$A$50=A2)*(($B$2:$B$50=B2)*($D$2:$D$50=D2)))))}

I need to find out how to show the result of 1, 2 and 3 etc. in Column E, F,
G etc. for each date ie. 20090608 = 4 ones. and 2 twos etc. and then again
for th next date etc.

I have tried with =IF(F2=1,SUM(IF($D$2:$D$50=D2,F$2:F$50,0)),0) but it sums
all ones despite of date is changing.

What can I do and is it possibile?


A B C D E F
G
code id date No count of 1 count of count of 3
1100 504147 20090608 2 0 2 0
1100 504147 20090608 0 0 0 0
1100 504184 20090608 1 4 0 0
1100 504185 20090608 1 0 0 0
1100 504203 20090608 1 0 0 0
1100 504207 20090608 1 0 0 0
1100 504210 20090609 3 0 0 3
1100 504210 20090609 0 0 0 0
1100 504210 20090609 0 0 0 0

 

[Max]

One interp on your intents
Assuming E1:G1 contains the numbers: 1, 2, 3, ...
In E2: =SUMPRODUCT(($C$2:$C$50=$C2)*($D$2:$D$50=E$1))
Copy across/fill down
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[oliverkat]

Thanks Max, You are a SUPER USER, I did not see the solution of using E1 ...
to hold the parameter of 1, 2 etc. and combine it with AND that way in a
formula.

oliverkat

 

[Max]

Welcome, oliverkat
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik