I have 2 distinct tabs on my spreadsheet. One called "New Rules Sample", and
the other called "QA Data Sheet". I'm trying to capture all of my relevant
data on the QA Data tab, and so far so good. I have a variety of information
there. However, when I attempted to capture the 2 following bits of Data (and
tested my formula) I could not make it work. The formula seems to work well
with words (e.g. "Refusal"), but not so well with numerical values. Any
suggestions? Below are the 2 "Array" formulas I'm trying to make work with
respect to reporting the frequency (Row H) of BAC levels 0.16% or greater:

=SUMPRODUCT((YEAR('New Rules
Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules
Sample'!H4:H3500))))

=SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500))))

Btw, Roger has been of incredible help to me, but I didn't want to tax him
further, so I'm putting this problem out to the group, lol!

It's actually simpler if the criteria involves numbers ..

For
> =SUMPRODUCT((YEAR('New Rules
> Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules
> Sample'!H4:H3500))))

Put it as:
=SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*('New Rules
Sample'!H4:H3500>=0.16))

and for:
> =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
> 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500))))

this should suffice:
=SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")*('New
Rules Sample'!H4:H3500>=0.16))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan the Man" wrote:
> I have 2 distinct tabs on my spreadsheet. One called "New Rules Sample", and
> the other called "QA Data Sheet". I'm trying to capture all of my relevant
> data on the QA Data tab, and so far so good. I have a variety of information
> there. However, when I attempted to capture the 2 following bits of Data (and
> tested my formula) I could not make it work. The formula seems to work well
> with words (e.g. "Refusal"), but not so well with numerical values. Any
> suggestions? Below are the 2 "Array" formulas I'm trying to make work with
> respect to reporting the frequency (Row H) of BAC levels 0.16% or greater:
>
> =SUMPRODUCT((YEAR('New Rules
> Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules
> Sample'!H4:H3500))))
>
> =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
> 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500))))
>
> Btw, Roger has been of incredible help to me, but I didn't want to tax him
> further, so I'm putting this problem out to the group, lol!

Hi Max!

I copied and pasted both of your suggestions and got a: #REF! error result
. HELP!

"Max" wrote:

> It's actually simpler if the criteria involves numbers ..
>
> For
> > =SUMPRODUCT((YEAR('New Rules
> > Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules
> > Sample'!H4:H3500))))
>
> Put it as:
> =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*('New Rules
> Sample'!H4:H3500>=0.16))
>
> and for:
> > =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
> > 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500))))
>
> this should suffice:
> =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")*('New
> Rules Sample'!H4:H3500>=0.16))
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Dan the Man" wrote:
> > I have 2 distinct tabs on my spreadsheet. One called "New Rules Sample", and
> > the other called "QA Data Sheet". I'm trying to capture all of my relevant
> > data on the QA Data tab, and so far so good. I have a variety of information
> > there. However, when I attempted to capture the 2 following bits of Data (and
> > tested my formula) I could not make it work. The formula seems to work well
> > with words (e.g. "Refusal"), but not so well with numerical values. Any
> > suggestions? Below are the 2 "Array" formulas I'm trying to make work with
> > respect to reporting the frequency (Row H) of BAC levels 0.16% or greater:
> >
> > =SUMPRODUCT((YEAR('New Rules
> > Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules
> > Sample'!H4:H3500))))
> >
> > =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
> > 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500))))
> >
> > Btw, Roger has been of incredible help to me, but I didn't want to tax him
> > further, so I'm putting this problem out to the group, lol!

That's due to a line break issue when you copy n paste from posts. In the
formula bar, just place your cursor on the 2nd line (that's the obvious line
break), then remove it by pressing Backspace, then ENTER. All should be ok.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan the Man" wrote:
> Hi Max!
>
> I copied and pasted both of your suggestions and got a: #REF! error result
> . HELP!

Actually Max, after I sent the second note I realized why I got that error (I
can be thick sometime).

I did get the formula to take, however when I went to test it, I am getting
a numerical result on my "QA Data Sheet" tab (where I'm keeping the data)
independent of the BAC results.

For example if I place a date in Row T that is within the date parameters of
2007 (e.g. Jan 1, 2007, March 4, 2007), I get a result of 2. My thought with
this array formula is that I should get a result of 2 if the result in Row H
is .16 or greater, AND the result in Row T is of the date paramter the
formula is describing (e.g. the month or year). The formula only seems to be
tallying its outcome numbers based upon Row T.

I hope that made sense? I tested it a few times before writing again to ask
for help. I do like the sense of accomplishment from figuring it out, but
this array formula just doesn't want to play

Thanks Max!

"Max" wrote:

> It's actually simpler if the criteria involves numbers ..
>
> For
> > =SUMPRODUCT((YEAR('New Rules
> > Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules
> > Sample'!H4:H3500))))
>
> Put it as:
> =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*('New Rules
> Sample'!H4:H3500>=0.16))
>
> and for:
> > =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
> > 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500))))
>
> this should suffice:
> =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")*('New
> Rules Sample'!H4:H3500>=0.16))
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Dan the Man" wrote:
> > I have 2 distinct tabs on my spreadsheet. One called "New Rules Sample", and
> > the other called "QA Data Sheet". I'm trying to capture all of my relevant
> > data on the QA Data tab, and so far so good. I have a variety of information
> > there. However, when I attempted to capture the 2 following bits of Data (and
> > tested my formula) I could not make it work. The formula seems to work well
> > with words (e.g. "Refusal"), but not so well with numerical values. Any
> > suggestions? Below are the 2 "Array" formulas I'm trying to make work with
> > respect to reporting the frequency (Row H) of BAC levels 0.16% or greater:
> >
> > =SUMPRODUCT((YEAR('New Rules
> > Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules
> > Sample'!H4:H3500))))
> >
> > =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
> > 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500))))
> >
> > Btw, Roger has been of incredible help to me, but I didn't want to tax him
> > further, so I'm putting this problem out to the group, lol!

Yes, the formula should work as you described.

But .. think I missed this line in your orig. post:
> .. the frequency (Row H) of BAC levels 0.16% or greater
(I was looking more at your 2 formulated attempts)

As 0.16% = 0.0016,

you just need correct the part: >=0.16 to read as: >=0.0016
within both formulas. Both should return the correct values.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan the Man" wrote:
> Actually Max, after I sent the second note I realized why I got that error (I
> can be thick sometime).
>
> I did get the formula to take, however when I went to test it, I am getting
> a numerical result on my "QA Data Sheet" tab (where I'm keeping the data)
> independent of the BAC results.
>
> For example if I place a date in Row T that is within the date parameters of
> 2007 (e.g. Jan 1, 2007, March 4, 2007), I get a result of 2. My thought with
> this array formula is that I should get a result of 2 if the result in Row H
> is .16 or greater, AND the result in Row T is of the date paramter the
> formula is describing (e.g. the month or year). The formula only seems to be
> tallying its outcome numbers based upon Row T.
>
> I hope that made sense? I tested it a few times before writing again to ask
> for help. I do like the sense of accomplishment from figuring it out, but
> this array formula just doesn't want to play
>
> Thanks Max!

Max you and Roger have been so very helpful tonight. I am appreciative. I
still can't seem to make the formula work when I manipulate the data in the 2
applicable Rows (H and T). I sent you the file on email if you have an
opportunity or willingness to look at it. If not I'll understand.

Best,

Dan

"Dan the Man" wrote:

> I have 2 distinct tabs on my spreadsheet. One called "New Rules Sample", and
> the other called "QA Data Sheet". I'm trying to capture all of my relevant
> data on the QA Data tab, and so far so good. I have a variety of information
> there. However, when I attempted to capture the 2 following bits of Data (and
> tested my formula) I could not make it work. The formula seems to work well
> with words (e.g. "Refusal"), but not so well with numerical values. Any
> suggestions? Below are the 2 "Array" formulas I'm trying to make work with
> respect to reporting the frequency (Row H) of BAC levels 0.16% or greater:
>
> =SUMPRODUCT((YEAR('New Rules
> Sample'!T4:T3500)=2007)*(ISNUMBER(SEARCH({">=0.16"},'New Rules
> Sample'!H4:H3500))))
>
> =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
> 07")*(ISNUMBER(SEARCH({"=>0.16"},'New Rules Sample'!H4:H3500))))
>
> Btw, Roger has been of incredible help to me, but I didn't want to tax him
> further, so I'm putting this problem out to the group, lol!

The prob was that there was a mixture of numbers and text within: 'New Rules
Sample'!H4:H3500. This can be treated via "adding" another condition into the
formula to ensure that only numbers would be involved (ignore text), ie:
ISNUMBER('New Rules Sample'!H4:H3500)

In QA Data Sheet,

Put instead in B11:
=SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*('New Rules
Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500)))

Put instead in B12:
=SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")*('New
Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500)))

Note that I've also changed in the above, back to using: >=0.16
from what I see in your descriptions in A11 & A12

One last comment. I noticed you had array-entered all your SUMPRODUCTs.
SUMPRODUCT doesn't require array-entering (CSE) unless TRANSPOSE is used
within. Just normal ENTER will do.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan the Man" wrote:
> Max you and Roger have been so very helpful tonight. I am appreciative. I
> still can't seem to make the formula work when I manipulate the data in the 2
> applicable Rows (H and T). I sent you the file on email if you have an
> opportunity or willingness to look at it. If not I'll understand.
>
> Best,
>
> Dan

Max I think Excel hates me, lol! I entered the data EXACTLY as you suggested
(into the proper cells and not as an Array), and when I went to taste the
variables in Rows T and H it wouldn't work. It's odd, because the other
variables you saw on my "QA Data Sheet" tab (the text) worked perfectly with
the formulas I was given thanks to Roger. These numerical references however
just don't want to cooperate. I felt stupid posting again after all the time
and effort you put into this for me...................

Dan

PS: Thank you very much for your time!

"Max" wrote:

> The prob was that there was a mixture of numbers and text within: 'New Rules
> Sample'!H4:H3500. This can be treated via "adding" another condition into the
> formula to ensure that only numbers would be involved (ignore text), ie:
> ISNUMBER('New Rules Sample'!H4:H3500)
>
> In QA Data Sheet,
>
> Put instead in B11:
> =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*('New Rules
> Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500)))
>
> Put instead in B12:
> =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")*('New
> Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500)))
>
> Note that I've also changed in the above, back to using: >=0.16
> from what I see in your descriptions in A11 & A12
>
> One last comment. I noticed you had array-entered all your SUMPRODUCTs.
> SUMPRODUCT doesn't require array-entering (CSE) unless TRANSPOSE is used
> within. Just normal ENTER will do.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Dan the Man" wrote:
> > Max you and Roger have been so very helpful tonight. I am appreciative. I
> > still can't seem to make the formula work when I manipulate the data in the 2
> > applicable Rows (H and T). I sent you the file on email if you have an
> > opportunity or willingness to look at it. If not I'll understand.
> >
> > Best,
> >
> > Dan
>

Hi Dan

The problem is that you have both text and numeric in Column H, where
the word Refusal appears.
Use the following formulae

=SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*
('New Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules
Sample'!H4:H3500)))

=SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"yyy mm")="Jan 07")*
('New Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules
Sample'!H4:H3500)))

--
Regards

Roger Govier


"Dan the Man" <(E-Mail Removed)> wrote in message
news:FB9B0305-D732-4E3D-B08E-(E-Mail Removed)...
> Max I think Excel hates me, lol! I entered the data EXACTLY as you
> suggested
> (into the proper cells and not as an Array), and when I went to taste
> the
> variables in Rows T and H it wouldn't work. It's odd, because the
> other
> variables you saw on my "QA Data Sheet" tab (the text) worked
> perfectly with
> the formulas I was given thanks to Roger. These numerical references
> however
> just don't want to cooperate. I felt stupid posting again after all
> the time
> and effort you put into this for me...................
>
> Dan
>
> PS: Thank you very much for your time!
>
> "Max" wrote:
>
>> The prob was that there was a mixture of numbers and text within:
>> 'New Rules
>> Sample'!H4:H3500. This can be treated via "adding" another condition
>> into the
>> formula to ensure that only numbers would be involved (ignore text),
>> ie:
>> ISNUMBER('New Rules Sample'!H4:H3500)
>>
>> In QA Data Sheet,
>>
>> Put instead in B11:
>> =SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007)*('New Rules
>> Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules Sample'!H4:H3500)))
>>
>> Put instead in B12:
>> =SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan
>> 07")*('New
>> Rules Sample'!H4:H3500>=0.16)*(ISNUMBER('New Rules
>> Sample'!H4:H3500)))
>>
>> Note that I've also changed in the above, back to using: >=0.16
>> from what I see in your descriptions in A11 & A12
>>
>> One last comment. I noticed you had array-entered all your
>> SUMPRODUCTs.
>> SUMPRODUCT doesn't require array-entering (CSE) unless TRANSPOSE is
>> used
>> within. Just normal ENTER will do.
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> xdemechanik
>> ---
>> "Dan the Man" wrote:
>> > Max you and Roger have been so very helpful tonight. I am
>> > appreciative. I
>> > still can't seem to make the formula work when I manipulate the
>> > data in the 2
>> > applicable Rows (H and T). I sent you the file on email if you have
>> > an
>> > opportunity or willingness to look at it. If not I'll understand.
>> >
>> > Best,
>> >
>> > Dan
>>