[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

VBA for index match and an IF statement to make a cumbersome formula easier

jsr

12/15/2006 1:03:00 AM

Hi All,
Looking for some assistance. built an Index Match that works great -
but I need to incorporate an IF statement based on varying levels of
revenue.

so here is the original formula:
=IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))

> B20 being a dollar value
A sample of the table looks like this
column A column B Column C
Monthly Revenue Account Director Account Manager
83,333 0 1
166,667 0 0

so, we are looking for how many staff are associate with a particular
level of revenue.

Here is where I need the IF statement and am wondering if VBA will do
the trick and what the code would be.
I want to incorporate an IF for = or > each of the levels of revenue so
basically I am looking for this:

IF (revenue =<83,000, then match the job title and return the number of
staff under that title
IF (revenue = < 166,667 then match the job title and return the number
of staff under that title

so on and so forth

thanks in advance for any assistance out there!
2 Answers

Martin Fishlock

12/15/2006 2:34:00 AM

0

jsr:

maybe you could use the sumproduct function

=sumproduct(--(a:a="Account Manager"),--(b:b>83,333),--(b:b<=16667))

or

=sumproduct(--(a:a="Account Manager"),--(b:b<=16667))-sum(above)

you need to adjust the references.



--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"jsr" wrote:

> Hi All,
> Looking for some assistance. built an Index Match that works great -
> but I need to incorporate an IF statement based on varying levels of
> revenue.
>
> so here is the original formula:
> =IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))
>
> > B20 being a dollar value
> A sample of the table looks like this
> column A column B Column C
> Monthly Revenue Account Director Account Manager
> 83,333 0 1
> 166,667 0 0
>
> so, we are looking for how many staff are associate with a particular
> level of revenue.
>
> Here is where I need the IF statement and am wondering if VBA will do
> the trick and what the code would be.
> I want to incorporate an IF for = or > each of the levels of revenue so
> basically I am looking for this:
>
> IF (revenue =<83,000, then match the job title and return the number of
> staff under that title
> IF (revenue = < 166,667 then match the job title and return the number
> of staff under that title
>
> so on and so forth
>
> thanks in advance for any assistance out there!
>
>

Tom Ogilvy

12/15/2006 3:26:00 AM

0

Perhaps use a pivot table and group on revenue.

--
Regards,
Tom Ogilvy

"jsr" <reitman@931media.com> wrote in message
news:1166144592.157154.254300@73g2000cwn.googlegroups.com...
> Hi All,
> Looking for some assistance. built an Index Match that works great -
> but I need to incorporate an IF statement based on varying levels of
> revenue.
>
> so here is the original formula:
> =IF(ISERROR(INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0))),0,INDEX(accountstaff,MATCH(B$20,INDEX(accountstaff,,1),0),MATCH($A55,INDEX(accountstaff,1,),0)))
>
>> B20 being a dollar value
> A sample of the table looks like this
> column A column B Column C
> Monthly Revenue Account Director Account Manager
> 83,333 0 1
> 166,667 0 0
>
> so, we are looking for how many staff are associate with a particular
> level of revenue.
>
> Here is where I need the IF statement and am wondering if VBA will do
> the trick and what the code would be.
> I want to incorporate an IF for = or > each of the levels of revenue so
> basically I am looking for this:
>
> IF (revenue =<83,000, then match the job title and return the number of
> staff under that title
> IF (revenue = < 166,667 then match the job title and return the number
> of staff under that title
>
> so on and so forth
>
> thanks in advance for any assistance out there!
>