Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
I've been searching the internet for the past 1.5 days and have yet to find
an example of the formula I'm looking for.... so if this is easy, I apologize in advance. I want a count of the number of cells where column "I" values = "NA" and column "D" values = "0". In the example below, only the 2nd & 5th rows would meet the criteria. I D NA 5 NA 0 xx/xx/xx 0 xx/xx/xx 0 NA 0 xx/xx/xx 7 NA 3 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Hi
=SUMPRODUCT(--($I$1:I100="NA"),--($D$1:$D$100=0),--($D$1:$D$100<"")) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Wilson" wrote in message ... I've been searching the internet for the past 1.5 days and have yet to find an example of the formula I'm looking for.... so if this is easy, I apologize in advance. I want a count of the number of cells where column "I" values = "NA" and column "D" values = "0". In the example below, only the 2nd & 5th rows would meet the criteria. I D NA 5 NA 0 xx/xx/xx 0 xx/xx/xx 0 NA 0 xx/xx/xx 7 NA 3 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Hi,
Maybe =SUMPRODUCT((I1:I7="NA")*(D1:D7=0)) which checks for zero or if 0 is really like in your post =SUMPRODUCT((I1:I7="NA")*(D1:D7="0")) Mike "Wilson" wrote: I've been searching the internet for the past 1.5 days and have yet to find an example of the formula I'm looking for.... so if this is easy, I apologize in advance. I want a count of the number of cells where column "I" values = "NA" and column "D" values = "0". In the example below, only the 2nd & 5th rows would meet the criteria. I D NA 5 NA 0 xx/xx/xx 0 xx/xx/xx 0 NA 0 xx/xx/xx 7 NA 3 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Good point I forgot about blank cells
"Arvi Laanemets" wrote: Hi =SUMPRODUCT(--($I$1:I100="NA"),--($D$1:$D$100=0),--($D$1:$D$100<"")) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Wilson" wrote in message ... I've been searching the internet for the past 1.5 days and have yet to find an example of the formula I'm looking for.... so if this is easy, I apologize in advance. I want a count of the number of cells where column "I" values = "NA" and column "D" values = "0". In the example below, only the 2nd & 5th rows would meet the criteria. I D NA 5 NA 0 xx/xx/xx 0 xx/xx/xx 0 NA 0 xx/xx/xx 7 NA 3 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Thanks for the quick answers guys....
Both methods are returning "0" This is data that I copied out of MSP. Would it matter if the entire column (where the "NA" or "xx/xx/xx" is stored) was formatted to be a date. There are around 5000 records. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
=SUMPRODUCT(--('Raw Data'!I4:I4902="NA"),--('Raw Data'!D4:D4902="0"),--('Raw
Data'!D4:D4902<"")) and =SUMPRODUCT(('Raw Data'!I4:I4902="NA")*('Raw Data'!D4:D4902="0")) returned ZEROS. I am baffled |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Maybe you have all data as strings and there are leading/trailing spaces
(like "0 ", or "NA ") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Wilson" wrote in message ... =SUMPRODUCT(--('Raw Data'!I4:I4902="NA"),--('Raw Data'!D4:D4902="0"),--('Raw Data'!D4:D4902<"")) and =SUMPRODUCT(('Raw Data'!I4:I4902="NA")*('Raw Data'!D4:D4902="0")) returned ZEROS. I am baffled |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Hi,
If you looking for ZERO remove the quotes Is NA typed in or is it an error #N/A returned by a formula? Mike "Wilson" wrote: =SUMPRODUCT(--('Raw Data'!I4:I4902="NA"),--('Raw Data'!D4:D4902="0"),--('Raw Data'!D4:D4902<"")) and =SUMPRODUCT(('Raw Data'!I4:I4902="NA")*('Raw Data'!D4:D4902="0")) returned ZEROS. I am baffled |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
thanks
NA is a value populated by my copy paste from MSProject. I don't believe it
is considered an error (i.e. there is no # in front of the NA). After the last post, I went to my 'raw data' worksheet and removed all "NA" values from the "I" column and used the following formula: =SUMPRODUCT(I5:I5000="NA") It returned zero. I then populated 3 fields with "a" and used =COUNTIF(I5:I5000,"a") and it returned 3, so it worked. In response to your comment on "if you are looking for zero..." I am looking for the value "0" not an unpopulated zero. Should I use quotes or no? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
I just Copied and replaced the NA values and I'm still getting zero
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Try this:
=SUMPRODUCT(--(A2:A10="NA"),ISNUMBER(B2:B10)*(B2:B10=0)) or this: =SUMPRODUCT((A2:A10="NA")*ISNUMBER(B2:B10)*(B2:B10 =0)) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Wilson" wrote in message ... I've been searching the internet for the past 1.5 days and have yet to find an example of the formula I'm looking for.... so if this is easy, I apologize in advance. I want a count of the number of cells where column "I" values = "NA" and column "D" values = "0". In the example below, only the 2nd & 5th rows would meet the criteria. I D NA 5 NA 0 xx/xx/xx 0 xx/xx/xx 0 NA 0 xx/xx/xx 7 NA 3 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Second one worked.... THANKS!!! |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
You're welcome.....I'm glad I could help.
Regards, Ron Microsoft MVP (Excel) "Wilson" wrote in message ... Second one worked.... THANKS!!! |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
have yet to find an example of the formula I'm looking for....
If you have Excel 2007, the newer formula might be: =COUNTIFS( A1:A7,"NA", B1:B7,0) I like to break up the ranges onto different lines, and take advantage of Ctrl+Shift+U to toggle the expansion of the formula bar. -- HTH :) Dana DeLouis "Wilson" wrote in message ... I've been searching the internet for the past 1.5 days and have yet to find an example of the formula I'm looking for.... so if this is easy, I apologize in advance. I want a count of the number of cells where column "I" values = "NA" and column "D" values = "0". In the example below, only the 2nd & 5th rows would meet the criteria. I D NA 5 NA 0 xx/xx/xx 0 xx/xx/xx 0 NA 0 xx/xx/xx 7 NA 3 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Apology
Hello,
... or this: =SUMPRODUCT((A2:A10="NA")*ISNUMBER(B2:B10)*(B2:B10 =0)) ... Funny. I thought of =SUMPRODUCT(--(PROPER(A2:A10)="NA"),--(--(B2:B10)=0)) [Just in case of some " NA" or "NA " etc.] Have fun, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...) | Excel Discussion (Misc queries) | |||
Apology for the "ddd" post | Excel Discussion (Misc queries) | |||
Apology | Excel Worksheet Functions | |||
advanced if????? | Excel Worksheet Functions | |||
Advanced use of IF | Excel Worksheet Functions |