Array formula using two columns and countif
Nope, that's not working. Getting #VALUE. Yes, the only thing I'm changing
is the time span - from using the date in AQ against today to using against
some future date.
"Bob Phillips" wrote:
Try again <g
=SUMPRODUCT(--(Current!J1:J1000="Value A"),
--(DATEDIF(Current!AQ1:AQ1000,$J$1,"D")180))
Is it just J1 instead of TODAY()?
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
"Dave" wrote in message
...
Not quite, but I explained it poorly. My fault. I'll try better this
time.
Let's say I do this with adding another column. This column calculates
the
age of the item based on a future date, say 5/31/06, which I place in J1.
Then my date would be calculated as =DATEDIF(AQ17,J1,"D") What I can do,
with the current formula you provided is instead of referencing my Current
Age column, I reference this Future column, which I'll just say is X for
visiblity. Therefore, I'd go with :
=SUMPRODUCT(--(Current!J1:J1000="Value A"),--(Current!X1:X1000180))
I'd like to avoid making the other column, and instead have the formula
calculate how many of "Value A" will be greater than 180 at the future
time
period, but this might be beyond feasability.
Thank you for your help in this. If this isn't possible, that's fine.
Another column is no big deal.
"Bob Phillips" wrote:
Dave,
Is this what you mean
=SUMPRODUCT(--(Current!J1:J1000="Value
A"),--(DATEDIF(Current!AQ1:AQ1000,TODAY(),"D")180))
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
"Dave" wrote in message
...
That worked perfectly, thanks!
Trying to build just one more bit of functionality on it and make it
lookforward to a date, like the end of a month. The H column in the
Current
worksheet is a calculated age {formula I use is
=DATEDIF(AQ17,TODAY(),"D") }.
Would it be simpler jsut to add another column on that calculates the
age
as
of a date and then point the forumla there instead of H, or can I
imbed
this
into the formula as well?
"Bob Phillips" wrote:
=SUMPRODUCT(--(Current!J1:J1000="Value A"),--(Current!H1:H1000180))
Note that SUMPRODUCT doesn't work with complete columns, you have to
specify
a range.
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
"Dave" wrote in message
...
Hello,
Having trouble getting Excel to compute a value based on two
columns
on
another sheet. What I need it to do is see if column A has the
value
I
specify, then look in column B and see if that date figure is
greater
than
180 days, and then count it. So I might have 45 that meet
criteria A,
but
then need to find items in that subset that meet criteria B.
My attempt was
=countif(if(COUNTIF(Current!J:J,"Value A",Current!H:H,"180")))
which
isn't
completing.
Any help is appreciated. Thanks!
|