| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional CountIf Question (asked different way)
1/1/2010 N/A
1/3/2010 Something 2/4/2010 Something 2/5/2010 N/A 2/9/2010 Something 2/22/2010 Something 5/10/2010 Something What formula will count all the dates in Febuary that don't have N/A in Column B? So the formula should return a value of 3. Thanks in advance! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional CountIf Question (asked different way)
=SUMPRODUCT(--(MONTH(A1:A7)=2),--(B1:B7<"N/A"))
Bob Umlas Excel MVP "Kevin" wrote in message ... 1/1/2010 N/A 1/3/2010 Something 2/4/2010 Something 2/5/2010 N/A 2/9/2010 Something 2/22/2010 Something 5/10/2010 Something What formula will count all the dates in Febuary that don't have N/A in Column B? So the formula should return a value of 3. Thanks in advance! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional CountIf Question (asked different way)
Assuming your N/A are actually #N/A returned from a formula then
=SUMPRODUCT(--(MONTH(A1:A7)=2), --(NOT(ISNA(B1:B7)))) else if they are text "N/A" then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(B1:B7<"N/A")) Like I said SumProduct... -- HTH... Jim Thomlinson "Kevin" wrote: 1/1/2010 N/A 1/3/2010 Something 2/4/2010 Something 2/5/2010 N/A 2/9/2010 Something 2/22/2010 Something 5/10/2010 Something What formula will count all the dates in Febuary that don't have N/A in Column B? So the formula should return a value of 3. Thanks in advance! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional CountIf Question (asked different way)
Thanks. I looked at that webpage but couldn't figure it out. Appreciate the
help!! "Jim Thomlinson" wrote: Assuming your N/A are actually #N/A returned from a formula then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(NOT(ISNA(B1:B7)))) else if they are text "N/A" then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(B1:B7<"N/A")) Like I said SumProduct... -- HTH... Jim Thomlinson "Kevin" wrote: 1/1/2010 N/A 1/3/2010 Something 2/4/2010 Something 2/5/2010 N/A 2/9/2010 Something 2/22/2010 Something 5/10/2010 Something What formula will count all the dates in Febuary that don't have N/A in Column B? So the formula should return a value of 3. Thanks in advance! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional CountIf Question (asked different way)
Take another crack at that web page. It takes a bit of time to wade through
it but it's worth it. -- HTH... Jim Thomlinson "Kevin" wrote: Thanks. I looked at that webpage but couldn't figure it out. Appreciate the help!! "Jim Thomlinson" wrote: Assuming your N/A are actually #N/A returned from a formula then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(NOT(ISNA(B1:B7)))) else if they are text "N/A" then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(B1:B7<"N/A")) Like I said SumProduct... -- HTH... Jim Thomlinson "Kevin" wrote: 1/1/2010 N/A 1/3/2010 Something 2/4/2010 Something 2/5/2010 N/A 2/9/2010 Something 2/22/2010 Something 5/10/2010 Something What formula will count all the dates in Febuary that don't have N/A in Column B? So the formula should return a value of 3. Thanks in advance! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional CountIf Question (asked different way)
Will do. Also, I noticed you can only do this up to about 25 rows before you
get an error (I guess that's the 255 array thing). Any way to get around that besides creating 4 of them (if you have 100 rows)? "Jim Thomlinson" wrote: Take another crack at that web page. It takes a bit of time to wade through it but it's worth it. -- HTH... Jim Thomlinson "Kevin" wrote: Thanks. I looked at that webpage but couldn't figure it out. Appreciate the help!! "Jim Thomlinson" wrote: Assuming your N/A are actually #N/A returned from a formula then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(NOT(ISNA(B1:B7)))) else if they are text "N/A" then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(B1:B7<"N/A")) Like I said SumProduct... -- HTH... Jim Thomlinson "Kevin" wrote: 1/1/2010 N/A 1/3/2010 Something 2/4/2010 Something 2/5/2010 N/A 2/9/2010 Something 2/22/2010 Something 5/10/2010 Something What formula will count all the dates in Febuary that don't have N/A in Column B? So the formula should return a value of 3. Thanks in advance! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional CountIf Question (asked different way)
Out of curiosity, what do the '--' do? I've never seen 2 dashes in a row
"Jim Thomlinson" wrote: Assuming your N/A are actually #N/A returned from a formula then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(NOT(ISNA(B1:B7)))) else if they are text "N/A" then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(B1:B7<"N/A")) Like I said SumProduct... -- HTH... Jim Thomlinson "Kevin" wrote: 1/1/2010 N/A 1/3/2010 Something 2/4/2010 Something 2/5/2010 N/A 2/9/2010 Something 2/22/2010 Something 5/10/2010 Something What formula will count all the dates in Febuary that don't have N/A in Column B? So the formula should return a value of 3. Thanks in advance! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional CountIf Question (asked different way)
coerces true / false into 1 / 0...
-- HTH... Jim Thomlinson "Kevin" wrote: Out of curiosity, what do the '--' do? I've never seen 2 dashes in a row "Jim Thomlinson" wrote: Assuming your N/A are actually #N/A returned from a formula then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(NOT(ISNA(B1:B7)))) else if they are text "N/A" then =SUMPRODUCT(--(MONTH(A1:A7)=2), --(B1:B7<"N/A")) Like I said SumProduct... -- HTH... Jim Thomlinson "Kevin" wrote: 1/1/2010 N/A 1/3/2010 Something 2/4/2010 Something 2/5/2010 N/A 2/9/2010 Something 2/22/2010 Something 5/10/2010 Something What formula will count all the dates in Febuary that don't have N/A in Column B? So the formula should return a value of 3. Thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| function question (sumif countif conditional) | Excel Worksheet Functions | |||
| Finding the list of the question I have asked | Excel Worksheet Functions | |||
| enable macros question not being asked when opening file | Excel Discussion (Misc queries) | |||
| I bet this has been asked before | Excel Discussion (Misc queries) | |||
| Question on Conditional COUNTIF | Excel Worksheet Functions | |||
Hybrid Mode
