sum based on three variables

[mark]

Hello Group

This was SENT yesterday but hasn't shown up on the newsgroup?

I have created a database of labels we send to outside fillers. What I
would like to do (without using autofilter or pivot tables) is sum the
number of labels sent to a filler by month. So for example range a1:a200 =
date with a short date in range b1:b200 (mmm - I thought it would be easier
to calculate), range c1:c200 = filler (we have five), range d1:d200 = label
description and finally range e1:e200 is the quantity of labels sent. So a
running total could be maintained by Month, Filler and total of labels sent
for the month.

I do not have permission to access VBA/Macros (Alt + F11 doesn't work, a
seperate issue any advise would be appreciated on this issue (the Network
Administrator is totally against upgrading the office installation on this
terminal and has more important people to look after than me (Violins
etc?!))

Any ideas would be appreciated - Office 97, Windows 2k

Thanks in advance for any help
Mark

 

[Frank Kabel]

Hi
use SUMPRODUCT for this e.g. try
=SUMPRODUCT(--(A1:A200>=DATE(2004,1,1)),--(A1:A200<DATE(2004,2,1)),--(C
1:C200="filler1"),--(D1200="label1"),E1:E200)

For an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

But you may also consider using a pivot table for this. see:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[mark]

Thank you Frank, is there a way though where I can sum the totals based on a
cell reference? For instance if in a seperate table where the information
is being returned to January (MMM-YY) was A2, could I not just reference
that cell?

mark

 

[Frank Kabel]

Hi
you can just replace the hardcoded values with a cell reference

 

[mark]

Yep got it - thanks

Hi
you can just replace the hardcoded values with a cell reference