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!

=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!

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!

1. No need to use datedif for days, a simple subtraction will do

=TODAY()-AQ17 (format as general not date)

2. You can use something like

=SUMPRODUCT(--(Current!J1:J1000="Value
A"),--(Current!AQ1:AQ1000=TODAY()-180))

obviously the formula won't work as is but I showed how it can be used
without using an extract column to create the days


"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!

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!

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!

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!

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!

Send me your workbook, it will be quicker <g

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

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