Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
Hi,
I am in trouble and your help..... I have an excel sheet that contains data for June and July. The data cannot be sorted and contains one day may be more than once. For instance After using countif Dates Values Dates No. of times date gets repeated 6/5/06 0 6/5/06 2 6/4/06 1 6/4/06 1 6/5/06 2 6/8/06 4 6/8/06 2 7/8/06 4 7/8/06 1 6/8/06 1 Using countif, I am able to count the no. of times these dates are repeating (in our example - 6/5/06 -2, 6/8/06 -1) You can see that there are some values besides different date, now what I need is to calulate how many times the value is above 2 for a particular day i.e 6/8/06 - 1. If day wise is not possible, please let me know if monthwise wld be possible. Please Help ASAP Thanks, Dinesh Advani -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
Use SUMPRODUCT:
For date of 1st July 2006: =SUMPRODUCT(--(A1:A5=DATE(2006,7,1)),--(B1:B52)) or month of July: =SUMPRODUCT(--(Month(A1:A5)=7),--(B1:B52)) HTH "dinadvani via OfficeKB.com" wrote: Hi, I am in trouble and your help..... I have an excel sheet that contains data for June and July. The data cannot be sorted and contains one day may be more than once. For instance After using countif Dates Values Dates No. of times date gets repeated 6/5/06 0 6/5/06 2 6/4/06 1 6/4/06 1 6/5/06 2 6/8/06 4 6/8/06 2 7/8/06 4 7/8/06 1 6/8/06 1 Using countif, I am able to count the no. of times these dates are repeating (in our example - 6/5/06 -2, 6/8/06 -1) You can see that there are some values besides different date, now what I need is to calulate how many times the value is above 2 for a particular day i.e 6/8/06 - 1. If day wise is not possible, please let me know if monthwise wld be possible. Please Help ASAP Thanks, Dinesh Advani -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
Hi,
Thanks a lot.. DA Toppers wrote: Use SUMPRODUCT: For date of 1st July 2006: =SUMPRODUCT(--(A1:A5=DATE(2006,7,1)),--(B1:B52)) or month of July: =SUMPRODUCT(--(Month(A1:A5)=7),--(B1:B52)) HTH Hi, [quoted text clipped - 29 lines] Thanks, Dinesh Advani -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
Hi,
Please help.......... the sumproduct is working fine but now I have a different problem. The sumproduct formula is in one file and I have a data in another file. Both the documents are linked and are at shared drive but the problem is when anybody other than me tries to open the file they receive a pop up box "Whether you to Update or Dont Update". Choosing either of the option changes the formula automatically. The new formula contains the path of the file but still it does'nt works Kindly help ASAP Thanks, DA dinadvani wrote: Hi, I am in trouble and your help..... I have an excel sheet that contains data for June and July. The data cannot be sorted and contains one day may be more than once. For instance After using countif Dates Values Dates No. of times date gets repeated 6/5/06 0 6/5/06 2 6/4/06 1 6/4/06 1 6/5/06 2 6/8/06 4 6/8/06 2 7/8/06 4 7/8/06 1 6/8/06 1 Using countif, I am able to count the no. of times these dates are repeating (in our example - 6/5/06 -2, 6/8/06 -1) You can see that there are some values besides different date, now what I need is to calulate how many times the value is above 2 for a particular day i.e 6/8/06 - 1. If day wise is not possible, please let me know if monthwise wld be possible. Please Help ASAP Thanks, Dinesh Advani -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct vs. countif | Excel Discussion (Misc queries) | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |