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

[jsr]

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!

 

[Martin Fishlock]

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.

 

[Tom Ogilvy]

Perhaps use a pivot table and group on revenue.