Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Hello.
I read all of the COUNTIF , AND threads but did not find a solution to my problem. - I have a column of dates on one worklsheet - I'm trying to do a countif when the date falls between 2 dates that I specify, something like COUNTif(sheet1!A:A,"=05/15/2009)+COUNTIF(sheet1!A:A,"<=5/21/2009), not no luck. Any assitance is welcome. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Hi,
Try this =sumproduct((range="05/15/2009")*(range="05/21/2009")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "NWO" wrote in message ... Hello. I read all of the COUNTIF , AND threads but did not find a solution to my problem. - I have a column of dates on one worklsheet - I'm trying to do a countif when the date falls between 2 dates that I specify, something like COUNTif(sheet1!A:A,"=05/15/2009)+COUNTIF(sheet1!A:A,"<=5/21/2009), not no luck. Any assitance is welcome. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
It's best to place your criteria in assigned cells, so that you can revise
the dates without having to change the formula itself. Start date in B1, End date in C1, =COUNTIF(A:A,"="&B1)-COUNTIF(A:A,""&C1) OR, not using entire columns ... except in XL07, =SUMPRODUCT((A1:A100=B1)*(A1:A100<=C1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "NWO" wrote in message ... Hello. I read all of the COUNTIF , AND threads but did not find a solution to my problem. - I have a column of dates on one worklsheet - I'm trying to do a countif when the date falls between 2 dates that I specify, something like COUNTif(sheet1!A:A,"=05/15/2009)+COUNTIF(sheet1!A:A,"<=5/21/2009), not no luck. Any assitance is welcome. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Thank you. I was close. It seems to work. Please advise what the &
performs in the foprmula so I can better understand. Mark :) "RagDyer" wrote: It's best to place your criteria in assigned cells, so that you can revise the dates without having to change the formula itself. Start date in B1, End date in C1, =COUNTIF(A:A,"="&B1)-COUNTIF(A:A,""&C1) OR, not using entire columns ... except in XL07, =SUMPRODUCT((A1:A100=B1)*(A1:A100<=C1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "NWO" wrote in message ... Hello. I read all of the COUNTIF , AND threads but did not find a solution to my problem. - I have a column of dates on one worklsheet - I'm trying to do a countif when the date falls between 2 dates that I specify, something like COUNTif(sheet1!A:A,"=05/15/2009)+COUNTIF(sheet1!A:A,"<=5/21/2009), not no luck. Any assitance is welcome. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Opss, I forgot one thing - each date in the column can have one of three
tiers (values I, II, or III). How would I expand the formula to do the counts by date and then by tiers? For example, there could be 3 occurances of 5/1/2009, one occurance woudl have Tier I, and the other two occurances woudl have Tier II. Thnak you . Mark :) "RagDyer" wrote: It's best to place your criteria in assigned cells, so that you can revise the dates without having to change the formula itself. Start date in B1, End date in C1, =COUNTIF(A:A,"="&B1)-COUNTIF(A:A,""&C1) OR, not using entire columns ... except in XL07, =SUMPRODUCT((A1:A100=B1)*(A1:A100<=C1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "NWO" wrote in message ... Hello. I read all of the COUNTIF , AND threads but did not find a solution to my problem. - I have a column of dates on one worklsheet - I'm trying to do a countif when the date falls between 2 dates that I specify, something like COUNTif(sheet1!A:A,"=05/15/2009)+COUNTIF(sheet1!A:A,"<=5/21/2009), not no luck. Any assitance is welcome. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Assume source dates in A2 down, tiers in B2 down
With Start date in B1, End date in C1, Tier in D1 you could use something like this: =SUMPRODUCT((A2:A100=B1)*(A2:A100<=C1)*)*(B2:B100 =D1)) Adapt to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "NWO" wrote: Opss, I forgot one thing - each date in the column can have one of three tiers (values I, II, or III). How would I expand the formula to do the counts by date and then by tiers? For example, there could be 3 occurances of 5/1/2009, one occurance woudl have Tier I, and the other two occurances woudl have Tier II. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Hi.
Received an error on the )*)*( part of the formula. "Max" wrote: Assume source dates in A2 down, tiers in B2 down With Start date in B1, End date in C1, Tier in D1 you could use something like this: =SUMPRODUCT((A2:A100=B1)*(A2:A100<=C1)*)*(B2:B100 =D1)) Adapt to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "NWO" wrote: Opss, I forgot one thing - each date in the column can have one of three tiers (values I, II, or III). How would I expand the formula to do the counts by date and then by tiers? For example, there could be 3 occurances of 5/1/2009, one occurance woudl have Tier I, and the other two occurances woudl have Tier II. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Sorry, it should read:
=SUMPRODUCT((A2:A100=B1)*(A2:A100<=C1)*(B2:B100=D 1)) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "NWO" wrote: Received an error on the )*)*( part of the formula. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Now I'm getting a #NUM after entering the formula. I still appreciate your
help. Help! "Max" wrote: Sorry, it should read: =SUMPRODUCT((A2:A100=B1)*(A2:A100<=C1)*(B2:B100=D 1)) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "NWO" wrote: Received an error on the )*)*( part of the formula. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
It should work fine. 2 possibilities happening over there
1. You used entire col ranges. You can't for sumproduct (not in xl2003 anyway) 2. If 1's not it, then its your data. You need to check your source data in cols A and B. Clear up all #NUM error values that's in there. -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "NWO" wrote: Now I'm getting a #NUM after entering the formula. I still appreciate your help. Help! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUntif AND...
Hi,
If you are using 2007 you can write =COUNTIFS(A2:A100,"="&B1,A2:A100,"<="&C1,B2:B100, D1) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "NWO" wrote: Now I'm getting a #NUM after entering the formula. I still appreciate your help. Help! "Max" wrote: Sorry, it should read: =SUMPRODUCT((A2:A100=B1)*(A2:A100<=C1)*(B2:B100=D 1)) -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "NWO" wrote: Received an error on the )*)*( part of the formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Need a little help w/ COUNTIF | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |