SUMPRODUCT ???

[Denys]

Good day everyone,

Having a problem with a formula.... I would like to know how many
times during a month a user entered GIC in Column C. In column A only
dates are entered and they are the dates the peson entered the data.
Column A , B, C,D,E & F have all the same length.

My problem is that if someone enterered GIC twice the same day, it
must only count for one as I want to know how many days during the
month this person worked on the GIC task.

The column A name is ZN and the column C name is ZN1.... Here's my
formula so far...

=SUM(IF(COUNTIF(ZN,ZN)=0,"",1/
COUNTIF(ZN,ZN))*(MONTH(ZN)=ROW(G11)*(ZN1="GIC")))

validated with Ctrl-Shift-Enter....

G1 is for January... until G12 for December

G11 gives me an answer of 3.4 where the answer should be 3....

Is there a way to ease the calculation?

I have spent a few hours now and I am getting discouraged...

Date Task SubTask
Volume Time

2007/11/06 Correction GIC
91 6:00
2007/11/07 Maintenance GIC
3 0:15
2007/11/07 Correction GIC
78 0:45
2007/11/08 Other Goals/Coaching 1
0:30
2007/11/08 Correction TIS Balancing 85
6:30
2007/11/09 Other Huddle
1 0:30
2007/11/09 Correction Follow up
1 0:15
2007/11/30 Other GIC
1 0:20
2007/11/30 Investigation TIS Balancing 3
1:30
2007/12/03 Other TIS Balancing
1 0:30
2007/12/03 Other Unplanned tasks 1
0:30
2007/12/03 Maintenance GIC
8 1:15
2007/12/03 Investigation Tax slips
7 1:30
2007/12/03 Correction TIS Balancing 25
3:00
2007/12/04 Other TIS Balancing
1 0:45




Thanks for your time

Denys

 

[Ron Coderre]

Try this ARRAY FORMULA (commited with Ctrl+Shift+Enter):

H11: =COUNT(1/((ZN1&MONTH(ZN)="GIC"&ROW())*(MATCH("GIC"&TEXT(ZN,"mmdd"),
ZN1&TEXT(ZN,"mmdd"),0)=(ROW(ZN)-1))))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Denys]

Hi Ron,

Thanks for your time.... Unfortunately, I get 0 as an answer for all
months.... I will try working with your formula in the meantime....

Have a nice day...

Denys

 

[Ron Coderre]

Did you remember to commit the formula
with CTRL+SHIFT+ENTER, instead of just ENTER?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Denys]

Did you remember to commit the formula
with CTRL+SHIFT+ENTER, instead of just ENTER?

Yes I did Ctrl+Shift+Enter.....
You see the {} appearing at both ends of the formula...

There must be a way to make it work.... must be

Thanks again Ron

Denys

 

[Dick Kusleika]

Yes I did Ctrl+Shift+Enter.....
You see the {} appearing at both ends of the formula...

There must be a way to make it work.... must be

You may have to change where he has "ROW()" to "ROW(G11)" if you're not
putting the formula on the same row as the month you're after.