|
Sumproduct to find monthly bonuses
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! |
Sumproduct to find monthly bonuses
=SUMPRODUCT(--(A2:A100="Bob"),--(C2:C100850),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 -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... 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! |
Sumproduct to find monthly bonuses
I only need the results from the last 30 rows of data. Database currently has
145 rows. "Peo Sjoblom" wrote: =SUMPRODUCT(--(A2:A100="Bob"),--(C2:C100850),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 -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... 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! |
Sumproduct to find monthly bonuses
=SUMPRODUCT(--(OFFSET($A$2,COUNTA($A2:$A$10000),,-30,)="Bob"),--(OFFSET($B$2,COUNTA($A2:$A$10000),,-30,)850))
-- Regards, Peo Sjoblom "Nick Krill" wrote in message ... I only need the results from the last 30 rows of data. Database currently has 145 rows. "Peo Sjoblom" wrote: =SUMPRODUCT(--(A2:A100="Bob"),--(C2:C100850),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 -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... 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! |
Sumproduct to find monthly bonuses
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))="Bo b"),--(INDEX($B$2:$B$10000,COUNTA($A$2:$A$10000)-29):INDEX($B$2:$B$10000,COUNTA($A$2:$A$10000))850 )) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... =SUMPRODUCT(--(OFFSET($A$2,COUNTA($A2:$A$10000),,-30,)="Bob"),--(OFFSET($B$2,COUNTA($A2:$A$10000),,-30,)850)) -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... I only need the results from the last 30 rows of data. Database currently has 145 rows. "Peo Sjoblom" wrote: =SUMPRODUCT(--(A2:A100="Bob"),--(C2:C100850),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 -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... 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! |
Sumproduct to find monthly bonuses
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 "Peo Sjoblom" wrote: 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))="Bo b"),--(INDEX($B$2:$B$10000,COUNTA($A$2:$A$10000)-29):INDEX($B$2:$B$10000,COUNTA($A$2:$A$10000))850 )) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... =SUMPRODUCT(--(OFFSET($A$2,COUNTA($A2:$A$10000),,-30,)="Bob"),--(OFFSET($B$2,COUNTA($A2:$A$10000),,-30,)850)) -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... I only need the results from the last 30 rows of data. Database currently has 145 rows. "Peo Sjoblom" wrote: =SUMPRODUCT(--(A2:A100="Bob"),--(C2:C100850),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 -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... 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! |
Sumproduct to find monthly bonuses
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
-- Regards, Peo Sjoblom "Stan" wrote in message ... 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 "Peo Sjoblom" wrote: 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))="Bo b"),--(INDEX($B$2:$B$10000,COUNTA($A$2:$A$10000)-29):INDEX($B$2:$B$10000,COUNTA($A$2:$A$10000))850 )) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... =SUMPRODUCT(--(OFFSET($A$2,COUNTA($A2:$A$10000),,-30,)="Bob"),--(OFFSET($B$2,COUNTA($A2:$A$10000),,-30,)850)) -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... I only need the results from the last 30 rows of data. Database currently has 145 rows. "Peo Sjoblom" wrote: =SUMPRODUCT(--(A2:A100="Bob"),--(C2:C100850),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 -- Regards, Peo Sjoblom "Nick Krill" wrote in message ... 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! |
| All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com