T.Valko function

Morning.
Back in January I'd posted looking for some help on a function, and T.Valko 
helped, solving my issue.
In using the function, I've run across something I was not expecting, and 
need to now solve that. 

The original post was: 
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=Steve&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6&mid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6

The problem that I've now recognized is that if there is a blank cell in my 
data on my source sheet, the first equation from the excel sheet provided 
will result in there being a false positive. 
I.e., it will show the count for all cells that are blank. 
In my initial function I'm checking to see how many names are missing, if 
any. 
As there'd normally have SOME value, regardless, I need to ignore blanks, 
and the placement of the blanks is not predictable, or standardized.

0
Utf
3/18/2010 8:10:10 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
586 Views

Similar Articles

[PageSpeed] 25

Try these...

For the count of misssing names:

=SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1)

To list those missing names (array entered):

=IF(ROWS(D$2:D2)>C$2,"",INDEX(Rng1,SMALL(IF(ISNA(MATCH(SubRng1,Rng2,0)),IF(SubRng1<>"",ROW(SubRng1))),ROWS(D$2:D2))))

-- 
Biff
Microsoft Excel MVP


"Steve" <Steve@discussions.microsoft.com> wrote in message 
news:B49D8372-0CEF-4540-9A21-9EA56C86289D@microsoft.com...
> Morning.
> Back in January I'd posted looking for some help on a function, and 
> T.Valko
> helped, solving my issue.
> In using the function, I've run across something I was not expecting, and
> need to now solve that.
>
> The original post was:
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=Steve&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6&mid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6
>
> The problem that I've now recognized is that if there is a blank cell in 
> my
> data on my source sheet, the first equation from the excel sheet provided
> will result in there being a false positive.
> I.e., it will show the count for all cells that are blank.
> In my initial function I'm checking to see how many names are missing, if
> any.
> As there'd normally have SOME value, regardless, I need to ignore blanks,
> and the placement of the blanks is not predictable, or standardized.
> 


0
T
3/18/2010 9:35:07 PM
Hi Biff,
Thank you for your reply, and willingness to pick this up again. I had 
thought it was working great until this anomaly.... Turns out now we have 
more than a handful of these files with blanks in my data set, so I want to 
rectify it.

Ok, tried the addition of the countblank in the first equation. I get a 
twofold response-- on one data set it works great (names), but with the other 
(numeric values), it still shows blank cells-- a numeric value. In this 
specific case-- 41. 
I did a run through with the equation evaluator, and think that I'm 
following the logic ok. Think being the keyword. 

Match runs through and returns ALL N/A#'s. ISNA then returns ALL True's. 
Sumproduct then returns 347-- the total count of the true responses from 
ISNA. 
Countblank then shows 306, leaving 41. 
However, all of my other tools show that all my data is accounted for. (I 
have a primary set of sumproduct eq'n's that do a numeric tally of the 
dataset's numeric side. They have two criteria to check, and then sum a 
third. Something Harlan Grove helped with back in '06.) 

In fact, in the data set that is working fine (names), the values with the 
first eq'n goes to zero.

While I'm still trying to run through the equation evaluator with the second 
equation, I just realized something.
The dataset that this new function does work with is all a text string-- 
names, etc....
The second dataset that the eq'n returns an erroneous response, is all 5 
digit numbers. I'd faced this in the beginning with what Harlan Grove had 
explained on the sumproduct, and he provided a datatype nullifier to nullify 
the possible datatype variances/conflicts from one worksheet to another. 
As such, because the function is doing what I'd described above, I'm now 
wondering if this is my issue-- datatype conflicts? 
If so, how can I nullify the datatype with these functions?

In my 2 criteria sumproducts, I used &"" (a single ampersand and two double 
quotes) to nulify this specific dataset. Would I do the same here? 

e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB=RngB_1)*(SumRng))

If so, what location in the equation would they be placed?

Again, thank you for your time.
Best.


"T. Valko" wrote:

> Try these...
> 
> For the count of misssing names:
> 
> =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1)
> 
> To list those missing names (array entered):
> 
> =IF(ROWS(D$2:D2)>C$2,"",INDEX(Rng1,SMALL(IF(ISNA(MATCH(SubRng1,Rng2,0)),IF(SubRng1<>"",ROW(SubRng1))),ROWS(D$2:D2))))
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Steve" <Steve@discussions.microsoft.com> wrote in message 
> news:B49D8372-0CEF-4540-9A21-9EA56C86289D@microsoft.com...
> > Morning.
> > Back in January I'd posted looking for some help on a function, and 
> > T.Valko
> > helped, solving my issue.
> > In using the function, I've run across something I was not expecting, and
> > need to now solve that.
> >
> > The original post was:
> > http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=Steve&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6&mid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6
> >
> > The problem that I've now recognized is that if there is a blank cell in 
> > my
> > data on my source sheet, the first equation from the excel sheet provided
> > will result in there being a false positive.
> > I.e., it will show the count for all cells that are blank.
> > In my initial function I'm checking to see how many names are missing, if
> > any.
> > As there'd normally have SOME value, regardless, I need to ignore blanks,
> > and the placement of the blanks is not predictable, or standardized.
> > 
> 
> 
> .
> 
0
Utf
3/18/2010 11:23:01 PM
It sounds like these 5 digits numbers might actually be TEXT strings. 
Numeric data like serial numbers, invoice numbers, etc. that start with 
leading zeros almost always cause problems if you're not careful.

>e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB=RngB_1)*(SumRng))

In that formula, what you're doing is concatenating an empty TEXT string to 
the end of the value:

cell_ref&""

So, if cell_ref = numeric 10, then cell_ref&"" = TEXT string 10. Numeric 
numbers and text numbers aren't equal when evaluated in most functions. 
Numeric 10 <> text 10. Notable exceptions are COUNTIF and SUMIF. They treat 
numeric numbers and text numbers as being equal. Numeric 10 = text 10.

Also, if cell_ref is an empty cell then cell_ref&"" might cause problems if 
you forget to account for it.

A...C
1....1
1

1
1

=SUMPRODUCT(--(A1:A5&""=C1&""))-COUNTBLANK(A1:A5)

=3

As you can see that is obviously not the result you expect.

See if this info helps. If not, then I might need to actually see the data 
for myself to see what's causing the problem.

-- 
Biff
Microsoft Excel MVP


"Steve" <Steve@discussions.microsoft.com> wrote in message 
news:288A1380-16DA-4E60-8BB7-29433AA18066@microsoft.com...
> Hi Biff,
> Thank you for your reply, and willingness to pick this up again. I had
> thought it was working great until this anomaly.... Turns out now we have
> more than a handful of these files with blanks in my data set, so I want 
> to
> rectify it.
>
> Ok, tried the addition of the countblank in the first equation. I get a
> twofold response-- on one data set it works great (names), but with the 
> other
> (numeric values), it still shows blank cells-- a numeric value. In this
> specific case-- 41.
> I did a run through with the equation evaluator, and think that I'm
> following the logic ok. Think being the keyword.
>
> Match runs through and returns ALL N/A#'s. ISNA then returns ALL True's.
> Sumproduct then returns 347-- the total count of the true responses from
> ISNA.
> Countblank then shows 306, leaving 41.
> However, all of my other tools show that all my data is accounted for. (I
> have a primary set of sumproduct eq'n's that do a numeric tally of the
> dataset's numeric side. They have two criteria to check, and then sum a
> third. Something Harlan Grove helped with back in '06.)
>
> In fact, in the data set that is working fine (names), the values with the
> first eq'n goes to zero.
>
> While I'm still trying to run through the equation evaluator with the 
> second
> equation, I just realized something.
> The dataset that this new function does work with is all a text string-- 
> names, etc....
> The second dataset that the eq'n returns an erroneous response, is all 5
> digit numbers. I'd faced this in the beginning with what Harlan Grove had
> explained on the sumproduct, and he provided a datatype nullifier to 
> nullify
> the possible datatype variances/conflicts from one worksheet to another.
> As such, because the function is doing what I'd described above, I'm now
> wondering if this is my issue-- datatype conflicts?
> If so, how can I nullify the datatype with these functions?
>
> In my 2 criteria sumproducts, I used &"" (a single ampersand and two 
> double
> quotes) to nulify this specific dataset. Would I do the same here?
>
> e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB=RngB_1)*(SumRng))
>
> If so, what location in the equation would they be placed?
>
> Again, thank you for your time.
> Best.
>
>
> "T. Valko" wrote:
>
>> Try these...
>>
>> For the count of misssing names:
>>
>> =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1)
>>
>> To list those missing names (array entered):
>>
>> =IF(ROWS(D$2:D2)>C$2,"",INDEX(Rng1,SMALL(IF(ISNA(MATCH(SubRng1,Rng2,0)),IF(SubRng1<>"",ROW(SubRng1))),ROWS(D$2:D2))))
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Steve" <Steve@discussions.microsoft.com> wrote in message
>> news:B49D8372-0CEF-4540-9A21-9EA56C86289D@microsoft.com...
>> > Morning.
>> > Back in January I'd posted looking for some help on a function, and
>> > T.Valko
>> > helped, solving my issue.
>> > In using the function, I've run across something I was not expecting, 
>> > and
>> > need to now solve that.
>> >
>> > The original post was:
>> > http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=Steve&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6&mid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6
>> >
>> > The problem that I've now recognized is that if there is a blank cell 
>> > in
>> > my
>> > data on my source sheet, the first equation from the excel sheet 
>> > provided
>> > will result in there being a false positive.
>> > I.e., it will show the count for all cells that are blank.
>> > In my initial function I'm checking to see how many names are missing, 
>> > if
>> > any.
>> > As there'd normally have SOME value, regardless, I need to ignore 
>> > blanks,
>> > and the placement of the blanks is not predictable, or standardized.
>> >
>>
>>
>> .
>> 


0
T
3/19/2010 2:14:11 AM
Morning again Biff.
Hope your weekend was a good one. Mine was a 3 day-er due to a furlough 
policy put in place to save money and keep everyone working... so far it's 
helped, and the money lost is offset by enjoying extra time off. 

Ok, back to it... I did try the &"" addition, and found a couple of things.
1- if I placed too many of them, it simply killed the Match, and ISNA 
elements of the equation. The ISNA got killed because no true responses were 
returned. And the Match only partially worked, and became a complex variable 
at that point. As the point of match is to either find an exact match, or 
fail, and when fail is returned, it provides the N/A# response, it appears to 
have been nullified, because, as you said-- the numeric 10 != the text 10. 

2- I wound up getting a -306 (the answer of the countblank).

I then kept reducing the &"" applications until I came to a single set for 
each.

=SUMPRODUCT(--(ISNA(MATCH(APN!$J$6:$J$352&"",A$15:A$104,0))))-COUNTBLANK(APN!$J$6:$J$352)

=IF(ROWS(L$5:L5)>L$3,"All Permits Acct'd 
For",INDEX(APN!$J$6:$J$352,SMALL(IF(ISNA(MATCH(APN!$J$6:$J$352&"",A$15:A$104,0)),IF(APN!$J$6:$J$352<>"",ROW(APN!$J$6:$J$352))),ROWS(L$5:L5))))


For the present, this appears to work. However, I have only tested it on one 
workbook with a single data set. Although, this particular dataset is one of 
the most complicated datasets we possess, so it seems to be a good test set. 

Your recognition of the 5 digit #'s being or acting like a text string is 
what precipitated the use of the &"" in the first place. We'd found numerous 
files and data sets in the beginning were responding oddly when we used the 
sumproduct as referenced last week, and this (&"") was Harlan's fix for it. 
The sumproduct has worked famously ever since, without fail- in my specific 
use. 
We found that the &"" appeared to act as a datatype nullifier to kill any 
datatype property that would prevent the sumproduct from recognizing two 
identical numbers of a differing datatype. 

As I read your previous post, and worked through the equation evaluator, it 
dawned on me the datatype variance might be my issue. 

Oh, and we never use leading zeroes. While there are datasets with them, 
we've worked out the code for stripping them when recognizing the data. 
Specifically because of the point you raised regardng them. 

If I find that any issues arise from the use of the &"" in this equation 
system you provided, I'll be back, and we can go at it again-- this time 
providing you a data set. 


again, thank you for your helps, and time.
Best.
SteveB.

"T. Valko" wrote:

> It sounds like these 5 digits numbers might actually be TEXT strings. 
> Numeric data like serial numbers, invoice numbers, etc. that start with 
> leading zeros almost always cause problems if you're not careful.
> 
> >e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB=RngB_1)*(SumRng))
> 
> In that formula, what you're doing is concatenating an empty TEXT string to 
> the end of the value:
> 
> cell_ref&""
> 
> So, if cell_ref = numeric 10, then cell_ref&"" = TEXT string 10. Numeric 
> numbers and text numbers aren't equal when evaluated in most functions. 
> Numeric 10 <> text 10. Notable exceptions are COUNTIF and SUMIF. They treat 
> numeric numbers and text numbers as being equal. Numeric 10 = text 10.
> 
> Also, if cell_ref is an empty cell then cell_ref&"" might cause problems if 
> you forget to account for it.
> 
> A...C
> 1....1
> 1
> 
> 1
> 1
> 
> =SUMPRODUCT(--(A1:A5&""=C1&""))-COUNTBLANK(A1:A5)
> 
> =3
> 
> As you can see that is obviously not the result you expect.
> 
> See if this info helps. If not, then I might need to actually see the data 
> for myself to see what's causing the problem.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Steve" <Steve@discussions.microsoft.com> wrote in message 
> news:288A1380-16DA-4E60-8BB7-29433AA18066@microsoft.com...
> > Hi Biff,
> > Thank you for your reply, and willingness to pick this up again. I had
> > thought it was working great until this anomaly.... Turns out now we have
> > more than a handful of these files with blanks in my data set, so I want 
> > to
> > rectify it.
> >
> > Ok, tried the addition of the countblank in the first equation. I get a
> > twofold response-- on one data set it works great (names), but with the 
> > other
> > (numeric values), it still shows blank cells-- a numeric value. In this
> > specific case-- 41.
> > I did a run through with the equation evaluator, and think that I'm
> > following the logic ok. Think being the keyword.
> >
> > Match runs through and returns ALL N/A#'s. ISNA then returns ALL True's.
> > Sumproduct then returns 347-- the total count of the true responses from
> > ISNA.
> > Countblank then shows 306, leaving 41.
> > However, all of my other tools show that all my data is accounted for. (I
> > have a primary set of sumproduct eq'n's that do a numeric tally of the
> > dataset's numeric side. They have two criteria to check, and then sum a
> > third. Something Harlan Grove helped with back in '06.)
> >
> > In fact, in the data set that is working fine (names), the values with the
> > first eq'n goes to zero.
> >
> > While I'm still trying to run through the equation evaluator with the 
> > second
> > equation, I just realized something.
> > The dataset that this new function does work with is all a text string-- 
> > names, etc....
> > The second dataset that the eq'n returns an erroneous response, is all 5
> > digit numbers. I'd faced this in the beginning with what Harlan Grove had
> > explained on the sumproduct, and he provided a datatype nullifier to 
> > nullify
> > the possible datatype variances/conflicts from one worksheet to another.
> > As such, because the function is doing what I'd described above, I'm now
> > wondering if this is my issue-- datatype conflicts?
> > If so, how can I nullify the datatype with these functions?
> >
> > In my 2 criteria sumproducts, I used &"" (a single ampersand and two 
> > double
> > quotes) to nulify this specific dataset. Would I do the same here?
> >
> > e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB=RngB_1)*(SumRng))
> >
> > If so, what location in the equation would they be placed?
> >
> > Again, thank you for your time.
> > Best.
> >
> >
> > "T. Valko" wrote:
> >
> >> Try these...
> >>
> >> For the count of misssing names:
> >>
> >> =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1)
> >>
> >> To list those missing names (array entered):
> >>
> >> =IF(ROWS(D$2:D2)>C$2,"",INDEX(Rng1,SMALL(IF(ISNA(MATCH(SubRng1,Rng2,0)),IF(SubRng1<>"",ROW(SubRng1))),ROWS(D$2:D2))))
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Steve" <Steve@discussions.microsoft.com> wrote in message
> >> news:B49D8372-0CEF-4540-9A21-9EA56C86289D@microsoft.com...
> >> > Morning.
> >> > Back in January I'd posted looking for some help on a function, and
> >> > T.Valko
> >> > helped, solving my issue.
> >> > In using the function, I've run across something I was not expecting, 
> >> > and
> >> > need to now solve that.
> >> >
> >> > The original post was:
> >> > http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=Steve&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6&mid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6
> >> >
> >> > The problem that I've now recognized is that if there is a blank cell 
> >> > in
> >> > my
> >> > data on my source sheet, the first equation from the excel sheet 
> >> > provided
> >> > will result in there being a false positive.
> >> > I.e., it will show the count for all cells that are blank.
> >> > In my initial function I'm checking to see how many names are missing, 
> >> > if
> >> > any.
> >> > As there'd normally have SOME value, regardless, I need to ignore 
> >> > blanks,
> >> > and the placement of the blanks is not predictable, or standardized.
> >> >
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
3/22/2010 4:30:01 PM
Good deal. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Steve" <Steve@discussions.microsoft.com> wrote in message 
news:7706FB2B-0211-4874-8033-C424CF4676E7@microsoft.com...
> Morning again Biff.
> Hope your weekend was a good one. Mine was a 3 day-er due to a furlough
> policy put in place to save money and keep everyone working... so far it's
> helped, and the money lost is offset by enjoying extra time off.
>
> Ok, back to it... I did try the &"" addition, and found a couple of 
> things.
> 1- if I placed too many of them, it simply killed the Match, and ISNA
> elements of the equation. The ISNA got killed because no true responses 
> were
> returned. And the Match only partially worked, and became a complex 
> variable
> at that point. As the point of match is to either find an exact match, or
> fail, and when fail is returned, it provides the N/A# response, it appears 
> to
> have been nullified, because, as you said-- the numeric 10 != the text 10.
>
> 2- I wound up getting a -306 (the answer of the countblank).
>
> I then kept reducing the &"" applications until I came to a single set for
> each.
>
> =SUMPRODUCT(--(ISNA(MATCH(APN!$J$6:$J$352&"",A$15:A$104,0))))-COUNTBLANK(APN!$J$6:$J$352)
>
> =IF(ROWS(L$5:L5)>L$3,"All Permits Acct'd
> For",INDEX(APN!$J$6:$J$352,SMALL(IF(ISNA(MATCH(APN!$J$6:$J$352&"",A$15:A$104,0)),IF(APN!$J$6:$J$352<>"",ROW(APN!$J$6:$J$352))),ROWS(L$5:L5))))
>
>
> For the present, this appears to work. However, I have only tested it on 
> one
> workbook with a single data set. Although, this particular dataset is one 
> of
> the most complicated datasets we possess, so it seems to be a good test 
> set.
>
> Your recognition of the 5 digit #'s being or acting like a text string is
> what precipitated the use of the &"" in the first place. We'd found 
> numerous
> files and data sets in the beginning were responding oddly when we used 
> the
> sumproduct as referenced last week, and this (&"") was Harlan's fix for 
> it.
> The sumproduct has worked famously ever since, without fail- in my 
> specific
> use.
> We found that the &"" appeared to act as a datatype nullifier to kill any
> datatype property that would prevent the sumproduct from recognizing two
> identical numbers of a differing datatype.
>
> As I read your previous post, and worked through the equation evaluator, 
> it
> dawned on me the datatype variance might be my issue.
>
> Oh, and we never use leading zeroes. While there are datasets with them,
> we've worked out the code for stripping them when recognizing the data.
> Specifically because of the point you raised regardng them.
>
> If I find that any issues arise from the use of the &"" in this equation
> system you provided, I'll be back, and we can go at it again-- this time
> providing you a data set.
>
>
> again, thank you for your helps, and time.
> Best.
> SteveB.
>
> "T. Valko" wrote:
>
>> It sounds like these 5 digits numbers might actually be TEXT strings.
>> Numeric data like serial numbers, invoice numbers, etc. that start with
>> leading zeros almost always cause problems if you're not careful.
>>
>> >e.g. =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB=RngB_1)*(SumRng))
>>
>> In that formula, what you're doing is concatenating an empty TEXT string 
>> to
>> the end of the value:
>>
>> cell_ref&""
>>
>> So, if cell_ref = numeric 10, then cell_ref&"" = TEXT string 10. Numeric
>> numbers and text numbers aren't equal when evaluated in most functions.
>> Numeric 10 <> text 10. Notable exceptions are COUNTIF and SUMIF. They 
>> treat
>> numeric numbers and text numbers as being equal. Numeric 10 = text 10.
>>
>> Also, if cell_ref is an empty cell then cell_ref&"" might cause problems 
>> if
>> you forget to account for it.
>>
>> A...C
>> 1....1
>> 1
>>
>> 1
>> 1
>>
>> =SUMPRODUCT(--(A1:A5&""=C1&""))-COUNTBLANK(A1:A5)
>>
>> =3
>>
>> As you can see that is obviously not the result you expect.
>>
>> See if this info helps. If not, then I might need to actually see the 
>> data
>> for myself to see what's causing the problem.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Steve" <Steve@discussions.microsoft.com> wrote in message
>> news:288A1380-16DA-4E60-8BB7-29433AA18066@microsoft.com...
>> > Hi Biff,
>> > Thank you for your reply, and willingness to pick this up again. I had
>> > thought it was working great until this anomaly.... Turns out now we 
>> > have
>> > more than a handful of these files with blanks in my data set, so I 
>> > want
>> > to
>> > rectify it.
>> >
>> > Ok, tried the addition of the countblank in the first equation. I get a
>> > twofold response-- on one data set it works great (names), but with the
>> > other
>> > (numeric values), it still shows blank cells-- a numeric value. In this
>> > specific case-- 41.
>> > I did a run through with the equation evaluator, and think that I'm
>> > following the logic ok. Think being the keyword.
>> >
>> > Match runs through and returns ALL N/A#'s. ISNA then returns ALL 
>> > True's.
>> > Sumproduct then returns 347-- the total count of the true responses 
>> > from
>> > ISNA.
>> > Countblank then shows 306, leaving 41.
>> > However, all of my other tools show that all my data is accounted for. 
>> > (I
>> > have a primary set of sumproduct eq'n's that do a numeric tally of the
>> > dataset's numeric side. They have two criteria to check, and then sum a
>> > third. Something Harlan Grove helped with back in '06.)
>> >
>> > In fact, in the data set that is working fine (names), the values with 
>> > the
>> > first eq'n goes to zero.
>> >
>> > While I'm still trying to run through the equation evaluator with the
>> > second
>> > equation, I just realized something.
>> > The dataset that this new function does work with is all a text 
>> > string-- 
>> > names, etc....
>> > The second dataset that the eq'n returns an erroneous response, is all 
>> > 5
>> > digit numbers. I'd faced this in the beginning with what Harlan Grove 
>> > had
>> > explained on the sumproduct, and he provided a datatype nullifier to
>> > nullify
>> > the possible datatype variances/conflicts from one worksheet to 
>> > another.
>> > As such, because the function is doing what I'd described above, I'm 
>> > now
>> > wondering if this is my issue-- datatype conflicts?
>> > If so, how can I nullify the datatype with these functions?
>> >
>> > In my 2 criteria sumproducts, I used &"" (a single ampersand and two
>> > double
>> > quotes) to nulify this specific dataset. Would I do the same here?
>> >
>> > e.g. 
>> > =sumproduct((ShtNm!RngA&""=RngA_1&"")*(ShtNm!RngB=RngB_1)*(SumRng))
>> >
>> > If so, what location in the equation would they be placed?
>> >
>> > Again, thank you for your time.
>> > Best.
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> Try these...
>> >>
>> >> For the count of misssing names:
>> >>
>> >> =SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))-COUNTBLANK(SubRng1)
>> >>
>> >> To list those missing names (array entered):
>> >>
>> >> =IF(ROWS(D$2:D2)>C$2,"",INDEX(Rng1,SMALL(IF(ISNA(MATCH(SubRng1,Rng2,0)),IF(SubRng1<>"",ROW(SubRng1))),ROWS(D$2:D2))))
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Steve" <Steve@discussions.microsoft.com> wrote in message
>> >> news:B49D8372-0CEF-4540-9A21-9EA56C86289D@microsoft.com...
>> >> > Morning.
>> >> > Back in January I'd posted looking for some help on a function, and
>> >> > T.Valko
>> >> > helped, solving my issue.
>> >> > In using the function, I've run across something I was not 
>> >> > expecting,
>> >> > and
>> >> > need to now solve that.
>> >> >
>> >> > The original post was:
>> >> > http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=Steve&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&p=1&tid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6&mid=487ddd9f-1b00-466c-ab5d-6f72a2f875a6
>> >> >
>> >> > The problem that I've now recognized is that if there is a blank 
>> >> > cell
>> >> > in
>> >> > my
>> >> > data on my source sheet, the first equation from the excel sheet
>> >> > provided
>> >> > will result in there being a false positive.
>> >> > I.e., it will show the count for all cells that are blank.
>> >> > In my initial function I'm checking to see how many names are 
>> >> > missing,
>> >> > if
>> >> > any.
>> >> > As there'd normally have SOME value, regardless, I need to ignore
>> >> > blanks,
>> >> > and the placement of the blanks is not predictable, or standardized.
>> >> >
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
T
3/23/2010 1:22:28 AM
Reply:

Similar Artilces:

can't browse file
I'm currently using Office 97 with Windows XP, it's never problem. But now, when I want to open my file from another location or another folder with MS office toolbar (open program and open file from toolbar), it can't browse any thing from that location or folder and Program not responding. Can anyone explain what is happening and how to stop it? anucha thailand Any chance you have lots of mapped drives to network shares? Or lots of removable drives (like USB or a memory card reader)?? If you disconnect those, does it help? (Just guesses, though.) k_anucha wrote: > ...

OWA contacts, can't do a find on DLs
Hi, just deployed OWA 2k3 and when i go to "To" click contacts and then try to find a DL it will not show (personal contacts not Global) what do you think is wrong thx monica Techie In Need <moni6422@yahoo.com> wrote: >just deployed OWA 2k3 and when i go to "To" click contacts and then try >to find a DL it will not show (personal contacts not Global) what do you >think is wrong Nothing's wrong with Exchange. Right-click the Contacts folder and display its property page. Click the Outlook Address Book tab and check the box lableled "Show thi...

Can't locate .mny file, don't have permission
I had a bad hard drive failure, but was able to recover my .mny file. However with a new hard drive and Money 2003 installed, I get an error message saying "Cannot locate Money Database.mny file or cannot access it, possibly because it is read only or don't have permission. I have gone through all the KB articles regarding this - changed read only status, ran file repair utilty and got no errors in log etc, but still can't access file. Although the .mny file is not read only, the directory is listed read only, and I cannot change it (or any other directory in Program File...

Can't send emails out of Outlook 2003
I just purchased a new laptop and Office 2003. I can receive emails but can't send out. I called Tech24 earlier today- who temporarily fixed the problem (or just lucked out messing with settings) but it's not working again. The settings are correct (I called my ISP). Any suggestions are welcome. If you use the Test Account Settings, what error message(s) do you get? Please post the exact text. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account ...

Messages won't send (Outlook 2002 SP2)
I perform the following steps 1) view outbox, which is empty 2) click new button on tool bar to create a new message 3) Create new message (in Word) and select the send button 4) message appears in outbox, but icon indicates the message is ope and the address etc are not in itallics. The icon looks like the one o the new button (i.e. an email with an open envelope behind it). I wil call this the 'open' state. 5) When I try to send/receive messages the message is not sent 6) Double click on the message and it is displayed in word 7) select the send button 8) The message is still in the...

Can't fine OEMInitGlobals...
Hello, what is the directory path and source file for the function OEMInitGlobals() ? I can't find it in alla WINCE600 directory.... -- Paolo Patierno Embedded Software Engineer Check out the following path... \WINCE600\PRIVATE\WINCEOS\COREOS\NK\OEMMAIN\oemglobal.c Regards, Prabu "Paolo Patierno" <PaoloPatierno@discussions.microsoft.com> wrote in message news:1801ABCF-1F74-4BB7-B282-1399F75A11DA@microsoft.com... > Hello, > what is the directory path and source file for the function > OEMInitGlobals() > ? I can't find it in alla WINC...

Don't buy SMALL BUSINESS OFFICE.
I recently purchased Office Small Business Edition for 2003 for my computer. It still calculates correctly, but they have changed the help algorithms to make it really tedious to use. Instead of the old key word lookups that I have always found in earlier versions, now they use some kind of computed lookup that take way too much time (after the almost instantaneous waits from the key words), and they have reduced the information content. For instance, I was running Excel and looking for a list of text functions. In the old systems, you could enter "text functions" and get an instan...

Excel startup: Can't find OPMDAT32.DLL
During Excel's startup I get an error message: Can't find OPMDAT32.DLL How can I fix this problem? Look under tools>add-ins, note which are checked, uncheck all of them and restart excel, if you don't get the message start by checking one by one and close and restart excel until yoiu get the error, that would be the culprit. Also look if you have something in your startup folder -- Regards, Peo Sjoblom "M" <M@discussions.microsoft.com> wrote in message news:D1C106DE-E583-4C0F-AAD6-3588DB36352B@microsoft.com... > During Excel's startup I get an error...

t
t ...

Can't scroll thru manufacturing BOM
We just upgraded from 7.5 to 9.0 SP3. The manufacturing BOM's look fine but we can't scroll through. The first record that comes up is item 'a' which really doesn't appear to exist. How do we correct? Do you still have a test environment with the same problem? If so does the problem go away on a BOM if you delete the 'a' component? Try that first. If it fixes the problem then you could remove all of the 'a' entries from the BOM line table (BM010115). -- Jim@TurboChef "MES" wrote: > We just upgraded from 7.5 to 9.0 SP3. The manufact...

Outlook Can't Reopen
I am running XP Professional with Service Pack 2 installed. Outlook 2003 also was updated via Outlook Service Packs. Problem: I open Outlook and it works and also places a icon in task bar. I exit Outlook (not close) and Icon remains in task bar. Also I cannot reopen Outlook in anyway. I must reboot. As PC closes to reboot an error message states the phrase CiceroUlWndFrame. I have used Dectect amd Repait but problem remains. Anthony ...

CDs don't play
I have WMP 11 for XP, using XP Professional. I can play music if I've loaded it onto the computer but it won't play CDs. Does anyone know why this is and how I can fix it? ...

Can't resize dialog when WS_SYSMENU set
I have a dialog with no caption bar, defined in the .rc file like so: STYLE WS_MINIMIZEBOX | WS_POPUP | WS_VISIBLE | WS_BORDER For window resizing, I catch the WM_NCHITTEST message in DefWindowProc and return HTBOTTOMRIGHT if the mouse is in the bottom-right of the dialog. I wan't to have a right-click task bar menu, so I add WS_SYSMENU to the above dialog style. This works, but windows no longer recognizes HTBOTTOMRIGHT as a resize area when the WS_SYSMENU flag is set. Can anyone explain this? I doubt its pertinent, but I also invoke SendMessage(WM_NCLBUTTONDOWN, HTCAPTION, NULL) on LB...

faq Progress don't have BN_LCLICKED?
Hi, all The progress control does not have BN_CLICKED,how can I make the progress control response to my leftdown mouse message? Thanks. "fcvcnet" <fcvcnet@163.com> wrote in message news:OqpuHbnQGHA.5812@TK2MSFTNGP10.phx.gbl... > Hi, all > The progress control does not have BN_CLICKED,how can I make the > progress > control response to my leftdown mouse message? > Thanks. I suspect you'll need to subclass it. -- Jeff Partch [VC++ MVP] Sublcass it. BN_CLICKED is issued by buttons, and a progress control is not a button, so it could not b...

Switched to NetBank and now can't ePay bills.
So after being using KeyBank bill pay flawlessly for like 4 years I decided to try and save 10 bucks a month by switching to NetBank for the free MS Money: http://www.netbank.com/banking_ofx.htm I ran into a problem with bill pay and here's a log of my "Bank Mail" correspondence with NetBank. ================== Sent: 02/08/2006 I've been able to sync my netbank accounts from within MS Money. Also, the available services for NetBank in Money are "Online Updates" and "Online bill payment". However, when I try to pay a bill I get a message "The ...

I can't believe this can't be done!
Outlook 2003. I've asked before & people have said no way but there must be some way to do it. In the calendar, monthly view, I would like today's date to display on the first week shown and subsequent weeks after that regardless if a new month has started or not. It makes no sense for me to enter the monthly view and be forced to see the three + weeks that have gone by and not be able to view the weeks coming up without going to them manually. There must be a way to do this. Help please. Bob Sorry - can't be done. Views are hard coded and this one is one of them. Ge...

outlook won't open #10
outlook won't open, i get a window saying that i need outlook express 4.01 or greater. i have outlook express 6, what gives..... In news:99BEA0AC-33EE-45D3-8422-2716CB560EC0@microsoft.com, gmcbucky <gmcbucky@discussions.microsoft.com> typed: > outlook won't open, i get a window saying that i need outlook express > 4.01 or greater. i have outlook express 6, what gives..... What version of Outlook? In control panel, do you have a Mail applet? If you click on it, what do you see? ...

DON'T UPGRADE!!
What a mess! After upgrading to 2005 all my personal accounts have basically been trashed as a result of 2005 importing my business account checks into my personal records (about $12,000.00 worth of "unassigned" expenses this month). Multiple crashes of app when logging on to MSN and several other issues. MS has said they are aware of problem(s) and would contact me with resolution (that was two weeks ago). I feel like a Beta tester, except that I PAID for this Gates catastrophe! Hopefully you backed up your data file before you upgraded. I'm sorry but I have zero s...

Don't add space between paragraphs of the same style
Hi, In Word, when defining a style, there is a paragraph format property titled "Don't add space between paragraphs of the same style". It's very convenient to add extra space before/after a block of paragraphs, such as a list. Is there any equivalent in Publisher or do I need to change the settings of each paragraph? Thanks. Raph ...

Set-Clipboard doesn't like whitespaces?
I can paste the word "now" to the windows clipboard, by opening a cmd prompt, and running the following code: C:\Users\marxp>C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -Sta "& {set-clipboard -text" "now"} But, if I insert a blank space into the string "now" (i.e., "no w"), I get the following error, which I don't understand. Can anyone point me in the right direction where I could learn why a blank space is an evil thing that should cause my code to not work, and how I can get around the problem?...

Correct the function WEEKNUMBER
The function weeknumber does not work properly. When typing Jan 1 2005 it should display 53 and not 1, when i typ jan 3rd (in colum 1) and =weeknumber(a2) in collum b it should display 1 and not 2. because the weeks in 2004 is 53 and not 52, is this my mistake or is this with excel that only counts up to week 52. I hope you can change this. I think you should read Chip Pearson's notes on =weeknum() http://www.cpearson.com/excel/weeknum.htm As well as excel's help for the =weeknum() function. Eric Wessels wrote: > > The function weeknumber does not work properly. When t...

Can't open files
Hello -- I'm sure this isn't the proper place to post my problem, but thought I'd start here anyways. Let me start off by saying that I'm extremely computer-stupid, so please forgive my ignorance. I'm running Windows Vista Ultimate, and yahoo email. For some reason, I'm unable to open attachments that have been sent to me, and saved to my computer. PLEASE HLP!! These are important documents that I really need to access. Thanks for your help -- It's not very informative to say you are not able to open attachments. What happens when you try? Is ther...

Can't Forward in Outlook
I just reinstalled everything on my computer, including copying over my .PST files. It all works fine except I can no longer forward messages. When I try to forward anything (even plain text) I'm told I don't have "appropriate permissions to perform this operation". How do I fix this nonsense? Thanks. Shannon Does it fail for all mail or only HTML? if only for html, it's a problem with IE (outlook uses IE to render HTML). -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart G...

Having trouble finding the right function
Hi, I am trying to get excell to auto fill a list of names onto a spreadsheet, the only twist is that if the name is blank it need to move onto the next available cell, what is the formula for this??? Thanks G Try this: B1=IF(A1>1,A1,"") drag it down column B (if that's what you r using) or to suit. Corey.... "g" <g@discussions.microsoft.com> wrote in message news:021334A2-488A-4347-8C36-4E3B0F6C575B@microsoft.com... > Hi, I am trying to get excell to auto fill a list of names onto a > spreadsheet, the only twist is that if the name is blank it nee...

Outlook
Can send email to Outlook, can access messages on ISP (Comcast) Webmail. Can't access server to download emails to PC. Ran all Norton Utlities, Removed and then reinstalled Outlook and Office apps. Still getting 0x800ccc0f error message - terminated by the server. Checked modem, checked router. All fine. ISP says nothing wrong on their end. Know of a fix? Tom does this help you determine the problem? http://support.microsoft.com/default.aspx?scid=kb;en-us;813514 -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Window...