sumproduct with and extra if function

S

StephiH

New Member
Joined
May 31, 2011
Messages
2
hi
I am trying to create a function that will allow me to sum up the sales of a specific sales rep during a data range.
I thought sumproduct would work but while I am able to get the information for the data range I am now stuck and can't figure out how to add the salesperson look up.

The sheet has several thousand lines in column B I have my 3 sales rep assigned to the sales.
Column P gives me the Transaction date while Column Q gives me the amount spend. Any ideas?

many thanks,
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Welcome to the board. Something like this:
=SUMPRODUCT(--(B2:B1000="Sales Rep"),--(P2:P1000=Date),(Q2:Q1000))
 
Upvote 0
Hi, I had a similar problem where I had to report sales within defined monthly periods. What I did was add an additional column to turn the date into a "mmm-yy" format, for example use =TEXT(B6,"mmm-yy") where column B contains the date.
Once I had done this I used pivot tables to give me the results by month.
The beauty of this is that there are only 12 entries in a year (instead of 365 if you left it as a pure date).
Hope this helps, Peter
 
Upvote 0
Hi, have a look at below, see if it helps:

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 61px"><COL style="WIDTH: 56px"><COL style="WIDTH: 18px"><COL style="WIDTH: 61px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Person</TD><TD style="FONT-WEIGHT: bold">Date</TD><TD style="FONT-WEIGHT: bold">Sales</TD><TD></TD><TD style="FONT-WEIGHT: bold">Variables</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #ccffcc">a</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">31/05/2011</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">1</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff99">a</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>a</TD><TD style="TEXT-ALIGN: right">01/06/2011</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">31/05/2011</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ccffcc">a</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">31/05/2011</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: right">7</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>b</TD><TD style="TEXT-ALIGN: right">31/05/2011</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>b</TD><TD style="TEXT-ALIGN: right">31/05/2011</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD style="FONT-WEIGHT: bold">Result</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>b</TD><TD style="TEXT-ALIGN: right">01/06/2011</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD><TD style="BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>c</TD><TD style="TEXT-ALIGN: right">31/05/2011</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>c</TD><TD style="TEXT-ALIGN: right">01/06/2011</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>c</TD><TD style="TEXT-ALIGN: right">01/06/2011</TD><TD style="TEXT-ALIGN: right">9</TD><TD></TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E7</TD><TD>=SUMPRODUCT(--($A$2:$A$10=E2),--($B$2:$B$10=E3),$C$2:$C$10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

The green rows are the ones where the variables (in the yellow cells) match and the blue cell is the resultant output (total number of sales for person a on date 31/05/2011)
 
Last edited:
Upvote 0
StephiH said:
hi
I am trying to create a function that will allow me to sum up the sales of a specific sales rep during a data range.
I thought sumproduct would work but while I am able to get the information for the data range I am now stuck and can't figure out how to add the salesperson look up.

The sheet has several thousand lines in column B I have my 3 sales rep assigned to the sales.
Column P gives me the Transaction date while Column Q gives me the amount spend. Any ideas?

many thanks,
Click to expand...
What version of Excel are you using?

When you say "data range", does that mean from a start date to an end date?

Like this:

Get the sum of sales for Joe that occurred from 1/1/2011 to 1/5/2011.
 
Upvote 0
Hi, Welcome to MrExcel.

Sample data...


Excel Workbook
ABC
1NameDateAmount
2Bart Simpson01/01/2011368.00
3Lisa Simpson02/01/2011277.00
4Grandpa Simpson03/01/2011474.00
5Lisa Simpson04/01/2011163.00
6Marge Simpson05/01/2011330.00
7Maggie Simpson01/01/2011346.00
8Bart Simpson07/01/2011472.00
9Lisa Simpson08/01/2011343.00
10Bart Simpson09/01/2011300.00
11Lisa Simpson10/01/2011343.00
12Tod Flanders01/01/2011100.00
13Bart Simpson12/01/2011301.00
14Moe Szyslak01/01/2011147.00
15Grandpa Simpson04/01/2011267.00
16Lisa Simpson05/01/2011458.00
17Marge Simpson06/01/2011436.00
18Maggie Simpson02/01/2011299.00
19Homer Simpson09/01/2011455.00
20Otto Mann01/01/2011399.00
Sheet1



Excel Workbook
ABCD
1NameStart DateEnd DateTotal Sales
2Bart Simpson01/01/201109/01/20111,140.00
Sheet2


Is that what you want to do?

Ak
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
SUMPRODUCT to pick up more than 1 column
Replies
2
Views
289
FormR
FormR
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
205
Domenic
D
Y
  • Question Question
VBA SUMPRODUCT not same as worksheet SUMPRODUCT
2
Replies
16
Views
534
Yamezz
Y
J
  • Question Question
Power Query - Column that Concatenates a column and a text version of a date.
Replies
2
Views
578
jaihawk8
J
B
  • Solved
Spilling Total using different ranges & If statement
2
Replies
12
Views
424
bearcub
B

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top