|
SUMPRODUCT ???
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 |
SUMPRODUCT ???
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" wrote in message ... 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 |
SUMPRODUCT ???
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 |
SUMPRODUCT ???
Did you remember to commit the formula
with CTRL+SHIFT+ENTER, instead of just ENTER? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Denys" wrote in message ... 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 |
SUMPRODUCT ???
On Mar 25, 1:08*pm, "Ron Coderre"
wrote: 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 -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Denys" wrote in message ... |
SUMPRODUCT ???
On Tue, 25 Mar 2008 10:20:38 -0700 (PDT), Denys
wrote: On Mar 25, 1:08*pm, "Ron Coderre" wrote: 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 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. -- Dick |
| All times are GMT +1. The time now is 02:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com