Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to find each day prior to first friday, monthly | Excel Worksheet Functions | |||
How to find how much to put aside monthly to reach savings goal | Excel Worksheet Functions | |||
need to work out bonuses | Excel Worksheet Functions | |||
I have a list of numbers (bus miles) I want to find the monthly a. | Excel Discussion (Misc queries) | |||
Bonuses and Penalties in Grade Books | Excel Worksheet Functions |