Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
SUMPRODUCT and OR | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) |