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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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