Sumproduct to find monthly bonuses

N

Nick Krill

I apologize for not being more elaborate in my initial query: How can I use
sumproduct on the last 30 rows of a database whose number of rows changes
daily?

My database has a list of first names in column A and sales results in
column C.

To compute monthly commission bonuses I need to multiply all those instances
where column A = “Bob†x all those same row instances where column C is
greater than $850

It has me vexed!
 
P

Peo Sjoblom

=SUMPRODUCT(--(A2:A100="Bob"),--(C2:C100>850),C2:C100)


will do it, replace the hardcoded criteria (bob and 850) with cell
references where you can type the criteria without editing the formula
 
N

Nick Krill

I only need the results from the last 30 rows of data. Database currently has
145 rows.
 
P

Peo Sjoblom

=SUMPRODUCT(--(OFFSET($A$2,COUNTA($A2:$A$10000),,-30,)="Bob"),--(OFFSET($B$2,COUNTA($A2:$A$10000),,-30,)>850))
 
P

Peo Sjoblom

Actually change that to

=SUMPRODUCT(--(OFFSET($A$2,COUNTA($A2:$A$10000),,-31,)="Bob"),--(OFFSET($B$2,COUNTA($A2:$A$10000),,-31,)>850))

or a non volatile version

=SUMPRODUCT(--(INDEX($A$2:$A$10000,COUNTA($A$2:$A$10000)-29):INDEX($A$2:$A$10000,COUNTA($A$2:$A$10000))="Bob"),--(INDEX($B$2:$B$10000,COUNTA($A$2:$A$10000)-29):INDEX($B$2:$B$10000,COUNTA($A$2:$A$10000))>850))
 
S

Stan

Peo, could you please explain (or point me to where I can read up on) what
the "--" does in formulas? I can not find help on that thing, but I use it in
some formulas that I got off this newsgroup. Have not idea how to change it /
what it means.
Thanks!
Stan
 
Top