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!