Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 150
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to find each day prior to first friday, monthly PaulRMcHanJr Excel Worksheet Functions 10 December 14th 06 11:32 PM
How to find how much to put aside monthly to reach savings goal Learning Excel for finances Excel Worksheet Functions 3 August 5th 06 01:47 AM
need to work out bonuses Grd Excel Worksheet Functions 4 May 19th 06 08:59 PM
I have a list of numbers (bus miles) I want to find the monthly a. MarilynD. Excel Discussion (Misc queries) 3 March 8th 06 08:30 PM
Bonuses and Penalties in Grade Books Kahlan Excel Worksheet Functions 5 October 12th 05 06:20 AM


All times are GMT +1. The time now is 12:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"