Sum based on three variables

Hello Group

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


0
8/6/2004 8:32:04 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
382 Views

Similar Articles

[PageSpeed] 40

Hi

One way would be something like:
=SUMPRODUCT(--(MONTH(A1:A200)=8)*(C1:C200=filler number)*(E1:E200))

Hope this helps.

-- 
Andy.


"mark" <lmarks2001-hbc@yahoo.co.uk> wrote in message
news:cevfm4$oru$1@titan.btinternet.com...
> Hello Group
>
> 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
>
>


0
Andy
8/6/2004 8:42:37 AM
Reply:

Similar Artilces:

MFC Dialog based control
Hello! I have a MFC dialog based application and want that the program does something that has to update the screen and keeps the program active or aware of user interaction when the user press a button, and want the program to stop doing so when the user press another button. How can I do this? Thank you very much If it's just a matter of refreshing the screen every few seconds I would just set a timer on and off with the buttons. In fact, you could use a Push Like check box and have it press in and out rather than having two buttons. When it's "On" you would have...

Variable field names?
Hi I am trying to execute the following code: - strSQL = strSQL & Nz([" & strCOM_Year & "], 0) where strCOM_Year is a string I want the system to use as a field name. It doesn't like this. Keeps saying 'can't find the field "|" referred to in you expression', even though I can clearly see in the debug window that the current value of strCOM_Year is "2006/2007". i.e. I am looking for the value in the field called 2006/2007. Any ideas? Stapes Stapes wrote: > Hi > > I am trying to execute the following code: - > > str...

why can't initialize HTREEITEM variable in VC6 release mode
I tried to initialize the HTREEITEM variables with NULL, the debug mode will initialize it to 0 correctly but the release mode doesn't. Anybody hit this problem and knows why? Thanks. It's impossible to answer without seeing any of your code. Is it possible your initialization is withing an #ifdef _DEBUG block or something like that? -- Jonathan Wood SoftCircuits http://www.softcircuits.com Available for consulting: http://www.softcircuits.com/jwood/resume.htm "lung han" <lhan@eitc.epson.com> wrote in message news:1139936944.252500.170030@g14g2000cwa.googlegrou...

Server based anti-spam software?
I'm looking for something similar to MailWasher- an anti-spam application for Exchange V5.5 server (on WinNT4). Is there such a thing? -- Julian Milano There are several, but I prefer GFI Mail Essentials. It runs on Exchange 5.5, 2000 & 2003 and can run on the Exchange Server or on a gateway. I use it to filter over 20,000 bogus messages from my 75 mailbox server each month, and send them to a centralized SPAM Mailbox which I can review at my leisure. You may also send items to individual mailboxes tagged as SPAM in the subject line or into a Junk Email Folder in the u...

Change Report Title Based on Source & Date?
I have a report with the following code behind: Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) If Format(Forms!frmCommission!cboStartDate, "mmyyyy") = Format(Forms!frmCommission!cboEndDate, "mmyyyy") Then Me.txtTitle = "Commission " & Format(Forms!frmCommission!cboStartDate, "mmm/yyyy") Else Me.txtTitle = "Commission " & _ Forms!frmCommission!cboStartDate & " To " & _ Forms!frmCommission!cboEndDate End If End Sub Private Sub Report_Open(Can...

Inserting Variables in String
I don't even know if I am using the proper terminolgy but here goes. I have three columns of variables that I would like to insert in a URL at specific points. I will try to describe what I am trying to do here in the following example: Cell A1 62.987 Cell B1 94.256 Cell C1 Alfrey Cell D1 http://www.mydomain.com/abcdVarA1efghVarB1hijkVarC1lmn.htm The above shows only Row 1 of many rows. I would like to insert the contents of A1 into the URL taking the place of "VarA", insert the contents of cell B1 into the section that says "VarB" and the contents ...

Can not update data on a query based form
I cannot update data in a query based form. The properties are set for update but the form will not accept changed data. Post the SQL statement of the query. Regards Jeff Boyce Microsoft Office/Access MVP "Arbys4131" <Arbys4131@discussions.microsoft.com> wrote in message news:C11E1418-E38C-4C02-A7D4-C05BE43DBBA3@microsoft.com... >I cannot update data in a query based form. The properties are set for > update but the form will not accept changed data. Arbys4131 wrote: > I cannot update data in a query based form. The properties are set > for update but t...

hide row based on cell value
i have a worksheet that i want to hide rows based on cell value. if cell B3 is blank, then i need rows 4,5 and 6 to be hidden, B3 contains data than row 4 is visible. if cell H3 is blank, then rows 5 and 6 need to be hidden. if b3 and h3 have data, then rows 4 and 5 are visible. if cell n3 is blank then row 6 is hidden. i have the following so far: Private Sub Worksheet_change(ByVal Target As Range) Application.EnableEvents = False If Target.Address(0, 0) = "P28" Then Select Case LCase(Target.Value) Case "target": MainMenu 'this ...

Returning Column Header based on Row and Value
Hi All, I've run into a problem trying to return a value from the header row based on locating a row value and a subsequent value within that row to define the column header needed. For example in the confusing example below imagine that the top row " A B C D" is the column headers and the column 1 "A B C D" is the row labels i've assigned. Given values of "C" and "D" I'd like to write a formula that searches down column 1 for "C" and then looks across the row for value "D" and returns the value of the column header &qu...

Count of values in a column based on values in another column
How do i count values based on values in another column. For example Col1 Col2 A 10 A A 20 B 12 B 15 B I want a formula for counting values in col2 where Col1 is "A". So the answer i am looking for is 2 Thanks Hi try =SUMPRODUCT((A2:A7="A")*(B2:B7<>"")) Regards JulieD "student" <agarwalp@lucent.com> wrote in message news:O$i5%23CfgEHA.3632@TK2MSFTNGP09.phx.gbl... > How do i count values based on values in another column. > > For example > > Col1 Col2 > A 1...

Adding rows of data based on first column
Not sure if I worded that correctly. Here's what I need to do: I have sales data for a month that is listed individually by date and item number. I need to go through the entire workbook and find each occurance of each item and add together the number of items ordered. Here is an example: Item TotalOrdered 119 1 121 1 121 1 121 1 182 1 182 1 206 1 206 1 223 1 589 1 589 1 As you can see, item 121 had 3 ordered, 589 had 2 ordered. Is there a formula I can use to do this automatically? Thanks. -- Excel Noobie Look in the help index for COUNTIF -- Don Guillett Microsoft MVP Excel Sales...

a CBaseDlg based on CDialog
Env: XP, VC++6.00 I have many dialog boxes which have a same feature: All of these dialogs have a button named "setting information". To avoid handling these buttons one by one, I make a CBaseDlg based on CDialog as follows, class CBaseDlg : public CDialog { public: CBaseDlg(UINT ID, CWnd* pParent = NULL); protected: virtual void DoDataExchange(CDataExchange* pDX); protected: CButton m_btn; virtual BOOL OnInitDialog(); afx_msg void OnBtnSettingInf(); } BEGIN_MESSAGE_MAP(CBaseDlg, CDialog) ON_BN_CLICKED(IDC_BTNSETTINGINF, OnBtnSettingInf) END_MESSAGE_MAP() BOOL CBaseDlg...

Query-Based Distribution Groups #2
I'm looking to create an LDAP query in Exchange 2003 that will show domain users that are currently logged on. This will allow me to create an email distribution group that contains only people who are in the office at that moment. Surely this must be possible as the query is searching Active Directory, but I've not been able to find a "logged on" attribute and I've trawled the web and newsgroups for answers but found nothing ... if anyone knows how to do this, I'd be very grateful of your help! Thanks, Rob "Robert Stokes" <rob@robertstokes.net...

moving sum
Hi, I have a list of monthly payments, and I want to create a column that sums those at the end of each year, six months or a quarter . EX: 1 0 1 0 2 4 4 0 1 0 1 6 .... What formula should I use in the second column to have a sum at every 12th,6th or 3rd row? Thanks. Neda Assume your data begins in A1. In B3 use: =SUM(A1:A3) Select B1:B3, grab the fill handle, and fill down. This will sum every 3 rows. For every six rows, in B6 use: =SUM(A1:A6) Select B1:B6 and fill down. Same process for every 12 rows. HTH Jason Atlanta, GA >-----Original Message----- >Hi, >I hav...

Variable Pay-rate after 5pm
After 5pm employees get paid a different rate per hour. Currently 4 entries for time "in" and "out" before and after lunch. Need to have Excel determine that after lunch, say, "1:45pm"-"6:15pm" includes 1.25 hour AFTER 5pm and needs to apply a different pay rate to that 1.25 hrs. I really don't want to have to add an extra 3 cells to the equation from"Clock in from lunch" to "4:59 PM" and "5:00 PM" to "Clock out for the day". Office 2007 Ulitmate Excel Win7 64bit Assume A1= normal ...

Access Control variable values in one PropertyPg from another Pro
hi all, i am unable to access the property page control values from another property page. // snippet of the code CPropertySheet dlg(_T("Hellow"), this, 0); one a; //First Property Page two b; //second Property Page three c; //third Property Page dlg.AddPage(&a); dlg.AddPage(&b); dlg.AddPage(&c); dlg.SetWizardMode(); dlg.DoModal(); //================================================ if the first property page contains the ComboBox with some values i am trying to acces the values in the second property page as one* m_Main2 = (one *) GetParent(); CString str; fo...

SUM problem #2
thanks. I can't get it work though. It returns *#value! * in all the cells! why is this -- R0bert ----------------------------------------------------------------------- R0bert0's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465 View this thread: http://www.excelforum.com/showthread.php?threadid=26506 Post the formula you are using. "R0bert0" <R0bert0.1dei9a@excelforum-nospam.com> wrote in message news:R0bert0.1dei9a@excelforum-nospam.com... > > thanks. > > I can't get it work though. It returns *#value! * in all the ...

Sum using probably a VLOOKUP
Hi, I'm looking for a way to make a sum of values in a column depending on the respective value of another column. Example: A1 = 1 A2 = 2 A3 = 1 B1 = 100 B2 = 300 B3 = 125 I'd like to be able to make a sum of B values when A values = 1. In that case, my sum would be 225. Thanks, Alex One way: =SUMIF(A:A,1,B:B) In article <OQ#LYB6hEHA.356@tk2msftngp13.phx.gbl>, "Alex Langlois" <alex.nospam@nospam.net> wrote: > Hi, > I'm looking for a way to make a sum of values in a column depending on > the respective value of another column. ...

Web based news browser
Don't know what has happened lately, but the the MS web based news browser functionality has decreased lately. Up until recently, searching on my name in a particular group or in the groups parent (microsoft.public.access) would return those posts that I made or replied to on the current date at the top of the list of posts. It no longer does that. Also, the Notify me of replies does not appear to be working properly. Although I have had several posts that had responses over the last week, none of these have resulted in an email notifying me of a response. Dale --...

lookup with two variables
I'm at my witts end. I am trying to write an "if" and "vlookup" formula using two criterias. For example, I have a monthly worksheet that I need to populate a %rate for primary and secondary. Some Employees have secondary and some do not. Each employee has a different rate for a primary and secondary for each year. So I want to write a formula that would look up the employees name and from the rate table look at their name and year and grab that %age rate. For example, If I was to look up Jane Smith' primary rate for 2007, if I look at the table...

Kowledge Base Question
Why do I see the Knowledge Base button in the Services section in the CRM Explorer Version, but not in the Outlook version? Our sales team are all using the outlook version, so they can't see the knowledge base. Whas up? It is exactly as described in the documentation. Knowledge base is only available in the web client. Quintin Prinsloo On 8/11/06 12:23 PM, in article 9C3DBB99-A7E1-40FB-9ABF-1D487C41FA26@microsoft.com, "MAC User" <MACUser@discussions.microsoft.com> wrote: > Why do I see the Knowledge Base button in the Services section in the CRM > Explorer Ver...

Report based on a record
How can I make a report, when it's opened, ask for user input? I need to have the user select a name from a list & report on that name's record. Is this done in the query for the report or the report itself? Thank You Don I almost always open a form first that has a control for the user to select or enter the criteria value(s). Then I add a button to open the report. There is sample code for this at http://www.mvps.org/access/reports/rpt0002.htm. -- Duane Hookom Microsoft Access MVP "blanch2010" wrote: > How can I make a report, when it...

Sum Product Function Question
Hello All: Quick question on SumProduct... Sheet 1 is a recap of processed returns for customers A1=Sprint A2=Verizon Column B is January Column C is February, and so on Sheet 2 is a "tracking log" of returns Column A contains the Date value, including month & year A1 = January 13 - E1 contains "Sprint" A2 = February 2 - E2 contains "Verizon" A3 = March - E3 contains "Sprint" I would like Sheet 1 to reflect the number of transactions within a specific month per customer, that is to say.... Jan Feb ...

User Control cannot access form variables
Hi All I am using VB Net 2008. I am trying to build a large application with multiple integrated functional modules like: - Operation - Accounting etc. I have tried to work on a premise where: - there would be a small root application which will build the basic environment for the entire application and display the main menu. Variables that control basic environment would be passed on from one form to another. - each function module would be a "Windows Forms Control Library". - when an option is selected from the menu in the root program, the form would be loa...

Using a dynamic formula to calculate results based on different assumptions
Hi everybody, I am looking for a simple way to resolve the following problem - don't worry if it sounds too architectural, you don't have to be an architect to solve this! I have a table of assumptions organised like this: Landuse Unit size Type 1 Type 2 Type 3............n Apt - 2bed 100m2 10% 50% 50% Apt - 1bed 60m2 20% 50% Shop 40m2 70% 50% ..... N I'd like to sum the total number of units based on the Type in another column (on another page) by di...