Sumproduct or not?

anogueira

New Member
Joined
Jan 25, 2005
Messages
7
Hi,

I am using sumproduct to get sales by product by manager by month like this:


Manager, Product, Jan05, Feb05...Dec05
A A 30 40 50
D Z 0 15 40

and I am doing this sumproduct((A10:A100=Manager)*(B10:B100=Product)*(C10:C100), and this works for january, but what if I want to do it for Feb05, or other month automaticly? Should I use sumproduct?

Thanks.

Antonio
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
Welcome to the Board!

You could use an indirect approach I think, but easier would be to mix relative and absolute referencing, and then copy over to suit.

=Sumproduct(--($A$10:$A$100=MANAGER)*--($B$10:$B$100=PRODUCT)*--(C10:C100))

HTH.
 

anogueira

New Member
Joined
Jan 25, 2005
Messages
7
I see your point, thanks.

But I am using a list box with the correspondent month, so that the user will choose the month and automaticly he will get only the amounts for that specific amout. The idea is to link the sum range (C10:C100) to the month selected in the list box. I tried to put formulas, but I get #value!.

Antonio
 

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
Ok, that changes things a bit. How about some code when a selection is made to just change the formula in the cell to the appropriate month?
 

anogueira

New Member
Joined
Jan 25, 2005
Messages
7

ADVERTISEMENT

Can you help you that please?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

anogueira said:
Can you help you that please?

No need to be that impatient... Give it some time.

Are those Jan05, etc. text values or true dates formatted as such? I suppose the dropdown list agrees qua data type with these entries.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
In your first post is the example your raw data?

Have you considered reorganizing it and then using a pivot table to summarize it?

Something like this for the data:

<html xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><meta name=ProgId content=Excel.Sheet><meta name=Generator content="Microsoft Excel 9"><link rel=File-List href="./Pivot_files/filelist.xml"><link rel=Edit-Time-Data href="./Pivot_files/editdata.mso"><link rel=OLE-Object-Data href="./Pivot_files/oledata.mso"><style></style></head><body link=blue vlink=purple><table x:str border=0 cellpadding=0 cellspacing=0 width=224 style='border-collapse: collapse;table-layout:fixed;width:168pt'> <col width=56 span=4 style='width:42pt'> <tr height=15 style='height:11.25pt'> <td height=15 width=56 style='height:11.25pt;width:42pt'>Manager</td> <td width=56 style='width:42pt'>Product</td> <td class=xl24 width=56 style='width:42pt'>Month</td> <td class=xl24 width=56 style='width:42pt'>Sales</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 style='height:11.25pt'>A</td> <td>A</td> <td class=xl25 align=right x:num="38353">Jan-05</td> <td align=right x:num>30</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 style='height:11.25pt'>D</td> <td>Z</td> <td class=xl25 align=right x:num="38353">Jan-05</td> <td align=right x:num>0</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 style='height:11.25pt'>A</td> <td>A</td> <td class=xl25 align=right x:num="38384">Feb-05</td> <td align=right x:num>40</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 style='height:11.25pt'>D</td> <td>Z</td> <td class=xl25 align=right x:num="38384">Feb-05</td> <td align=right x:num>15</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 style='height:11.25pt'>A</td> <td>A</td> <td class=xl25 align=right x:num="38412">Mar-05</td> <td align=right x:num>50</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 style='height:11.25pt'>D</td> <td>Z</td> <td class=xl25 align=right x:num="38412">Mar-05</td> <td align=right x:num>40</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=56 style='width:42pt'></td> <td width=56 style='width:42pt'></td> <td width=56 style='width:42pt'></td> <td width=56 style='width:42pt'></td> </tr> <![endif]></table></body></html>
 

anogueira

New Member
Joined
Jan 25, 2005
Messages
7
Aladin, Jan05 are true dates.

Norie,
I already thought doing that, the thing is that the month info will come from SAP in the internet, and it is easier for me to treat the data like that.
 

Forum statistics

Threads
1,147,686
Messages
5,742,623
Members
423,744
Latest member
bkirtland

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
Top