>= in array

  • Thread starter Thread starter aaronrm
  • Start date Start date
A

aaronrm

I have an array that works fine in its current form. What I would like
to do however is add an additional parameter that use less than.
=SUM(IF($D3&Lists!$A$32=Rep&Year,Revenue)). What I want to do is add a
month parameter for examples that would only add where the month is
less than 5.

something like:

={SUM(IF($D3&Lists!$A$32&<Lists!$A$32=Rep&Year&monthno,Revenue))}

Thanks
 
Hi!

No need for an array formula.

I'm assuming that Rep and Year (Rep&Year) are concatenated named ranges.
Where is the month data?

Something like this:

=SUMPRODUCT(--(Rep&Year=$D3&Lists!$A$32),--(MONTH(range_for_your_month)<5),Revenue)

Biff
 
I am not familiar with the sumproduct command but I tried a few things
and couldn't get it to work. Based on your response, there may be a
miss understanding though. Rep, Year, Monthno, revenue are all named
ranges. The cell references are the criteria that I am suming in my
nested array. I am not using the & for concatenation. Thanks
 
(e-mail address removed) wrote...
I have an array that works fine in its current form. What I would like
to do however is add an additional parameter that use less than.
=SUM(IF($D3&Lists!$A$32=Rep&Year,Revenue)). What I want to do is add a
month parameter for examples that would only add where the month is
less than 5.

something like:

={SUM(IF($D3&Lists!$A$32&<Lists!$A$32=Rep&Year&monthno,Revenue))}
Click to expand...

The formula immediately above is a syntax error, specifically, you
can't have a '&' immediately followed by '<'.

It looks like D3 combined with Lists!A32 resolves to a particular Rep
and Year. If you want Rep and Year to remain equality criteria, and you
want to add month as a less than criteria, then you need to use
*SEPARATE* criteria, ($D3&List!$A$32=Rep&Year) AND
(SomethingElse<Monthno). You'd be better off using SUMPRODUCT (whether
you want to believe that or not),

=SUMPRODUCT(($D3=Rep)*(List!$A$32=Year)*(SomethingElse<Monthno),Revenue)
 
Hi!
I am not using the & for concatenation.
Click to expand...
=SUM(IF($D3&Lists!$A$32=Rep&Year,Revenue))
Click to expand...

Well, that's exactly what's happening in your formula!

Try this:

=SUMPRODUCT(--(Rep=$D3),--(Year=Lists!$A$32),--(MONTH(Monthno)<5),Revenue)

If this doesn't work then you need to provide more DETAILS. Basically, what
you want to do is really simple but "we" need to be given ALL the facts to
provide the correct solution. It's kind of difficult to figure out what you
have when you don't tell us but for the most part we do a pretty good job of
it.!

Biff
 
Back
Top