Array formula using two columns and countif

[Guest]

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!

 

[Bob Phillips]

=SUMPRODUCT(--(Current!J1:J1000="Value A"),--(Current!H1:H1000>180))

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)

 

[Guest]

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?

 

[Peo Sjoblom]

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

 

[Bob Phillips]

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)

 

[Guest]

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:X1000>180))

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]

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)

 

[Guest]

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]

Send me your workbook, it will be quicker <g>

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)