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
706 Views

Similar Articles

[PageSpeed] 41

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:

Corrupt Money file
Hi - I'm hoping that someone can help me figure out what is going on with our Money file. Money froze up this morning and my husband hard booted to exit the program. He also reinstalled Money 2004 to our desktop computer. However, we are unable to open our Money file - it says it is corrupt or not a Money file. I've tried restoring from our back up files (both from floppy and from CD that we have consistently kept backed up), but the restored file also gets the same error message. We recently networked out two computers and now access our Money file from both computer...

Can't archive
When I try to archive my old mail (older than Jan 1, 2004), it looks like its starting to archive, and then it suddenly stops. Sometimes there's no error message - sometimes I get one saying that my password couldn't be found. Why would I need to use a password for emails on my own system, not from an email server? Larry ...

excel CountIF function #2
Hello, I have 2 columns in which first column is Sales name and another is date. I want to know the number of times, did the salemans visited for a particular date. I tried with countif function and cretiria used be and condition, since there is two conditions which has to fulfill. Please mail me if someone knows it? =SUMPRODUCT(--(A1:A100="Bill),--(B1:B100=--"2004/08/12")) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Rajashree" <rajashree@mangaloreuniversity.ac.in&...

Need offset function help, I think
I have 10 products rows with a cost in column BW of rows 21 thru 30, i.e., cells BW21 down to BW30. Those 10 products have the # of units sold each month, for 22 months, shown in rows 41 thru 50, in columns E thru Z, so column E is month #1 ... col Z is month #22. The total range is cells E40 thru Z40. There is a cost factor vector that has six factors in row 70, cells G70 thru L70. These are to be used =91for all time periods=92 and for all products. Any time there is a sale of a product in a certain month, I want to enter six months of cost associated with producing the item...

INDEX FUNCTION
Can anyone help with the INDEX Function? I want to post a file to freefilehosting.com so others can view the file. Could someone tell me how to post the file? I'd start at Debra Dalgleish's site: http://contextures.com/xlFunctions03.html And maybe... Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble Gulfman100 wrote: > > Can anyone help with the INDEX Functio...

Why won't ADDRESS work nested inside SUMIF?
If I do this: =ADDRESS(4,MATCH(E37,A2:JW2,0)) I get: $L$4 If I do: =SUMIF(A3:JW3,"Units",ADDRESS(4,MATCH(E37,A2:JW2,0)):JW4) I get an error. However if I do: =SUMIF(A3:JW3,"Units",INDIRECT(G1):JW4) I get the answer I want. However, I'd prefer to not have a helper cell. Please help! Some lateral thoughts, with emphasis on resolving the core issues you raised in your other, earlier thread Let's say you have col data in groups of 3 cols, with headers in row2 (eg: Period1, Period2, etc), where each header is placed only in the l...

Can't get bar code font to work in EXCEL 2003
I have a simple formula that converts a part number to a 3 of 9 bar code font in Excel 2003. Works great on my computer but a co-worker, who also has Excel 2003, can't get this to work. On her PC it show the 3 of 9 font in the drop down box but in the cell it is not converting it to a bar code image. Any suggestions? Thanks, Tony Most likely the font is not installed properly... She needs to reinstall the font. It can be done through Control Panel -> Fonts if you have the font available. "Tony" wrote: > I have a simple formula that conve...

Office Outlook 2003 won't work!
I have tried every help program I could find and followed each step and still get this message when trying to send or receive email...."The operation failed. An object could not be found." That's it, then I click ok and its gone, try to send again and the same message comes back up. Its configured to my ISP comcast, I tried everything, any ideas anyone? Have you searched the Microsoft Knowledge Base for that error message? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the ...

Compare objects based on IComparable<T> with unknown T
I need to implement a method that processes two objects. Among other things, the method may want to try to compare the two objects, which should use the CompareTo method, provided the two objects are the same class and the class implements IComparable<T>. void process(object o1, object o2) { if (o1.GetType().Equals(o2.GetType())) { Type t = o1.GetType(); if t implements IComparable<T> where T is not known then ??? compare o1 and o2 using their CompareTo method ??? ...

Can't move mailbox
My question is similar to "Can't Purge Mailbox". I have one mailbox left to move in a migration from exchange 2000 to 2003, and it will not move. I can move it between 2000 stores and servers but not to exchange 2003. I recieve the following error when I try to move it: - <summary isWarning="false" errorCode="0xc1054005"> The MAPI call failed. MAPI or an unspecified service provider. ID no: 80004005-0000-00000000 There are no corrupted messages, I have checked each one. The mailbox size is under 200mb. This is the only mailbox left to move??? I ...

Can't get to resource description info
We recently moved to a Citrix environment and seemed to have lost acces to the Resource Description utility. I can launch it and see the choices for tables, fields, and windows, but when I pick one the trouble begins. If I pick tables, I get an error message saying: An open operation on table olfd_files failed because the path does not exist When I click ok, I get another dialogue message: Scrolling window link table not on form Next I get what looks like a VB error: Unhandled form exception: Form load failed EXCEPTION_CLASS_FORM_MEMORY SCRIPT_CMD_OPENFR If I try to get to field d...

How to call a JS function on the form loading?
How to call the function validate() on the form loading? There is a form in which another program will fill some text boxes automatically. I need this JS function to test the value of text boxes and give user a alert if any text box is empty. <script type="text/javascript"> function validate() { var t1 = document.getElementById('<%=txtOrderProduct.ClientID%>'); var t2 = document.getElementById('<%=txtOrderBy.ClientID%>'); if(t1.value == "" && t2.value == "") { alert("Empty'); } }...

Customer Phone # Should be Displayed at POS (but isn't)
Once a customer is selected, their address is displayed at the POS -- but their phone number isn't. This a ridiculous omission, IMO. We contact clients by phone 99% of the time, with the other 1% being via email. Please add the display of phone number (& and cell and email!) to the customer info displayed in the POS screen. This kind of omission suggests to me that the software is not being tested in realistic retail conditions. It might technically WORK right, but it's not performing how retailers need. Perhaps a better testing method is needed to get "the retail real...

GPO doesn't work for specific OU
Hello, I created a new OU for a single Win 2003 Std R2 SP2 Term Server. I then right-clicked on the OU and chose "Create & Link a GPO here". Under "Links", I right-clicked on the OU and there are checks next to "Enforced" and "Link Enabled". I then edited the GP for the OU by right-clicking on the GPO and choosing "Edit". I did a test edit by going to: User Conf \ Admin Temp \ Start Menu and Taskbar and set "Remove Run Menu from Start Menu" to Enabled. It's not taking, and I can't seem to find the...

single user can't access reports
I have a single user, who received a new machine recently with a new install of CRM. He can't access reports. I checked his permissions and he has a role that other managers have. Those other managers can read & run reports. THis manager gets an unauthorized 401 error. He can't access either in outlook client or web client. However, I can log on as this person on my laptop, launch web client and see the reports he should be seeing. I have uninstalled and reinstalled both crm & sql - no dice. I am at a loss of what to do next. His windows updates are current, his...

Administrative users don't inherit Active Directory permissions?
Trying to set Active Directory permissions I noticed that the permissions on administrative users don't inherit from their container. Is this by design? Hello Neil, Thanks for your post. Before we go further I would like to confirm what "Administrative users" you refer to? Are you trying to modify certain user/group object permission from ADUC console or ADSIEdit console? Please let us know the detailed situation and symptoms. There are several ways to apply permissions. Microsoft provides two tools: ADSI Edit (AdsiEdit.msc) and DSACLS (Dsacls.exe). Both tools ...

Is there a function that can ...?
Good Day. I hope someone can help me with this little problem. Source data is a list of Names (col A) with corresponding numerical values (cols B to F). Each Name can have multiple, varying occurrences within this list. I wish to summarise, in another worksheet, the sum for each individual person. I am using sumif to sum each person’s values, which is working fine. The problem is how to have the summary table automatically find and list the names from the source, with the name occurring only once, with the numerical value being the sum of all the values for that person’s multiple occurr...

Cisco Unified CallConnector for Dynamics service don't start
i try to implement the Cisco Callconnector for CRM4.0 v4.03 but after installation i get this error in the event log If someone could help me, thanks in advance Type de l'événement : Erreur Source de l'événement : .NET Runtime 2.0 Error Reporting Catégorie de l'événement : Aucun ID de l'événement : 5000 Date : 26/09/2008 Heure : 11:41:15 Utilisateur : N/A Ordinateur : Description : EventType clr20r3, P1 c4serverservice.exe, P2 4.0.3.0, P3 486153c5, P4 mscorli...

i have fields that are formatted for text that randomly convert t.
i have fields that in the original spreadsheets are formatted as "text" that appear as date when ultimately converted to a .csv. This happens purely randomly and all the sheets and fields in which they appear have been formatted as "text". In spite of this certain numbers appear as "date" formatted in the csv file. any ideas. Thanks Dave The CSV don't change any formatting, it's when you import it into Excel it changes, you can rename the file to a TXT extension, open it from file>open and the text import wizard will open, if you click next twic...

vlookup worksheet function for external excel files
I am using vlookup to retrieve data in an external excel file -i.e. the data I am trying to retrieve is not stored in the same workbook nor network drive as the records I am trying to find data for. Unfortunately, vlookup requires that the data source excel sheet file is open if it is to return records. Do you know if there is a way to retrieve data using vlookup while keeping the source closed. Thanks and regards, Gustavo I don't think =vlookup() has this requirement. Are you using =indirect() with =vlookup()? Gustavo wrote: > > I am using vlookup to retrieve data in an ex...

Send/Receive doesn't work until changing folders.
An Outlook 2000 client on WinXP w/ an Exchange 5.5 email server, new mail does not arrive in the inbox until the folder is changed, like from inbox to deleted items. Then all the new Email messages come into the inbox. Clicking on Send/Receive also retreives them. I though new messages automaticall go to the inbox on Exchange. ...

Couldn't enable OWA
I tried to enable OWA per user from ADUC and system manager. When I click apply after OWA enabled I receive the error that "The directory service attribute or value already exists" and OWA remains disabled. "Facility: LDAP Provider. ID No: 8007200d. Microsoft Active Directory - Exchange Extension." Any help would be appreciated. Many thanks before ...

I don't want to graph "null" values?
I am plotting values over time using Excel's graphing feature. The graph takes data from an adjacent worksheet. I have defined the graph to take data from a large range in the work sheet but not all cells have data in them. I add one piece of data each day to the spreadsheet. The new point is graphed each day. However, all of the null cells following my last point are also plotted as zero. These cells have a "null" value in them not the number zero. How can I stop my plotted graph with the last numerical value? Thanks, Jim Go to Tools - Options - Chart. Select: ...

Word 2003 view doesn't show space at document top and bottom
I have Word 2003 and Windows 7. All at once, when I open a new document (Ctrl+n) or sometimes an old existing one, the view, which is in Print Layout, gets rid of the space at the top and the bottom of the page. I have to "work around" and click View<Header and Footer<Close. Then it is ok as long as I have the document open. But when I save and close the document and open it again, the same thing happens - the space is gone from the top and bottom. I don't know the correct terminology to describe this, but I hope you can understand. How does this happen,...

Can't Delete Corrupted Appointment
Using Outlook 2007. I have a recurring appointment that I can't delete. It shows a Start date of "None" and an End date of "Mon 1/1/1601 12:00 AM". The Recurrence Pattern is "every Wednesday from 3:00 PM to 4:00 PM. When I try to open the appointment I get the error box: Cannot open this item. The end date you entered occurs before the start date. When I try to delete it I get the error box: The move, copy, or deletion cannot be completed. The items might have been moved or deleted, or you may not have suffieient permission. If the item was sent as a task ...