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" wrote in message
...
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

"Dan the Man" wrote:
.. 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.

Since you have difficulty getting it to work over there, I've sent over the
file with the formulas implemented in B11 and B12 for your reference.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Roger,

Believe our findings and suggestions to Dan are identical <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max and Roger are AWESOME! I feel so silly because I copied and pasted the
formula into my spreadsheet EXACTLY as you gave it to me (I swear), and I
couldn't get it to work properly. The one you sent me however WORKS like a
dream. I don't get it, but I am VERY thankful to you and Roger for your help

On a positive note, I was however successful last night in doing something
similar with the Rows which contain text (e.g. Row G-name of court), but
wasn't sure how to do this with Rows containing dates (e.g. Row T-date of
Intake Interview).

Is the formula for tracking dates off of my "New Rules" tab, and placing it
onto my "QA Data Tab" as easy? I'm almost afraid to ask? My intention with
some of the date rows is to track total clients (based upon their Intake
Dates-Row T, and their Exit Interview Dates-Row Z) by the applicable month as
well as the annual total.

=SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007) -and-

=SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")

Unfortunately the above formulas returned a "0" result upon testing of the
data in Row T (when the applicable dates were placed there) so I must be
missing something (I do try on my own to figure it out before reaching out
for help, lol).

Again, thank you so VERY much to Max and Roger for your help!

Respectfully,

Dan


"Max" wrote:

"Dan the Man" wrote:
.. 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.

Since you have difficulty getting it to work over there, I've sent over the
file with the formulas implemented in B11 and B12 for your reference.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

try:

=SUMPRODUCT(--(TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07"))

"Dan the Man" wrote:

Max and Roger are AWESOME! I feel so silly because I copied and pasted the
formula into my spreadsheet EXACTLY as you gave it to me (I swear), and I
couldn't get it to work properly. The one you sent me however WORKS like a
dream. I don't get it, but I am VERY thankful to you and Roger for your help

On a positive note, I was however successful last night in doing something
similar with the Rows which contain text (e.g. Row G-name of court), but
wasn't sure how to do this with Rows containing dates (e.g. Row T-date of
Intake Interview).

Is the formula for tracking dates off of my "New Rules" tab, and placing it
onto my "QA Data Tab" as easy? I'm almost afraid to ask? My intention with
some of the date rows is to track total clients (based upon their Intake
Dates-Row T, and their Exit Interview Dates-Row Z) by the applicable month as
well as the annual total.

=SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007) -and-

=SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")

Unfortunately the above formulas returned a "0" result upon testing of the
data in Row T (when the applicable dates were placed there) so I must be
missing something (I do try on my own to figure it out before reaching out
for help, lol).

Again, thank you so VERY much to Max and Roger for your help!

Respectfully,

Dan


"Max" wrote:

"Dan the Man" wrote:
.. 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.

Since you have difficulty getting it to work over there, I've sent over the
file with the formulas implemented in B11 and B12 for your reference.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks Toppers. I was also able to extrapolate appropriately from your
formula to change the parameters when searching by year (e.g. 2007). For some
reason nothing was working right yesterday with respect to Excel and
formulas. Today, everything is going well. Whew! Knock on wood!

Dan

"Toppers" wrote:

try:

=SUMPRODUCT(--(TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07"))

"Dan the Man" wrote:

Max and Roger are AWESOME! I feel so silly because I copied and pasted the
formula into my spreadsheet EXACTLY as you gave it to me (I swear), and I
couldn't get it to work properly. The one you sent me however WORKS like a
dream. I don't get it, but I am VERY thankful to you and Roger for your help

On a positive note, I was however successful last night in doing something
similar with the Rows which contain text (e.g. Row G-name of court), but
wasn't sure how to do this with Rows containing dates (e.g. Row T-date of
Intake Interview).

Is the formula for tracking dates off of my "New Rules" tab, and placing it
onto my "QA Data Tab" as easy? I'm almost afraid to ask? My intention with
some of the date rows is to track total clients (based upon their Intake
Dates-Row T, and their Exit Interview Dates-Row Z) by the applicable month as
well as the annual total.

=SUMPRODUCT((YEAR('New Rules Sample'!T4:T3500)=2007) -and-

=SUMPRODUCT((TEXT('New Rules Sample'!T4:T3500,"mmm yy")="Jan 07")

Unfortunately the above formulas returned a "0" result upon testing of the
data in Row T (when the applicable dates were placed there) so I must be
missing something (I do try on my own to figure it out before reaching out
for help, lol).

Again, thank you so VERY much to Max and Roger for your help!

Respectfully,

Dan


"Max" wrote:

"Dan the Man" wrote:
.. 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.

Since you have difficulty getting it to work over there, I've sent over the
file with the formulas implemented in B11 and B12 for your reference.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---