Join two formulas

I have this formula:
=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--(MONTH('2003-2004'!$A
$4:$A$10000)=2),'2003-2004'!$E$4:$E$10000)

And I only want  this to be true if:


{=SUM((builder="m")*(servicedby="Dave")*nc)}

I don't think it is as easy as joining two together, but I tried:

=IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--(YEAR('2003-2004'!$A$4
:$A$10000)=2004,--(MONTH('2003-2004'!$A$4:$A$10000)=2),('2003-2004'!$E$4:$E$
10000))),1))

I also tried:

=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--(MONTH('2003-2004'!$A
$4:$A$10000)=2),--(TEXT('2003-2004'!$A$4:$A$10000)="Dave"),'2003-2004'!$E$4:
$E$10000)

My ideas are better than my knowledge of Excel!
Better buy a third book.

Joe


0
4/28/2004 12:31:05 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
437 Views

Similar Articles

[PageSpeed] 0

Correct me if I'm wrong, but it looks like you want to sum 
E4:E10000 where:

1. The date in A4:A10000 falls during Feb. 2004
2. The named range "builder" equals "m"
3. The named range "servicedby" equals "Dave".

Assuming your named ranges are of the same dimension 
(size) as E4:E10000, then try:

=SUMPRODUCT((YEAR('2003-2004'!$A$4:$A$10000)=2004)*(MONTH
('2003-2004'!$A$4:$A$10000)=2)*(builder="m")*
(servicedby="Dave")*'2003-2004'!$E$4:$E$10000)

HTH
Jason
Atlanta, GA


>-----Original Message-----
>I have this formula:
>=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
(MONTH('2003-2004'!$A
>$4:$A$10000)=2),'2003-2004'!$E$4:$E$10000)
>
>And I only want  this to be true if:
>
>
>{=SUM((builder="m")*(servicedby="Dave")*nc)}
>
>I don't think it is as easy as joining two together, but 
I tried:
>
>=IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--
(YEAR('2003-2004'!$A$4
>:$A$10000)=2004,--(MONTH('2003-2004'!$A$4:$A$10000)=2),
('2003-2004'!$E$4:$E$
>10000))),1))
>
>I also tried:
>
>=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
(MONTH('2003-2004'!$A
>$4:$A$10000)=2),--(TEXT('2003-2004'!$A$4:$A$10000)
="Dave"),'2003-2004'!$E$4:
>$E$10000)
>
>My ideas are better than my knowledge of Excel!
>Better buy a third book.
>
>Joe
>
>
>.
>
0
jason.morin (561)
4/28/2004 12:52:11 PM
Hi
how are your both names 'builder' and 'servicedby' 
defined. That is which column do they represent. After 
this it should be quite easy.

>-----Original Message-----
>I have this formula:
>=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
(MONTH('2003-2004'!$A
>$4:$A$10000)=2),'2003-2004'!$E$4:$E$10000)
>
>And I only want  this to be true if:
>
>
>{=SUM((builder="m")*(servicedby="Dave")*nc)}
>
>I don't think it is as easy as joining two together, but 
I tried:
>
>=IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--
(YEAR('2003-2004'!$A$4
>:$A$10000)=2004,--(MONTH('2003-2004'!$A$4:$A$10000)=2),
('2003-2004'!$E$4:$E$
>10000))),1))
>
>I also tried:
>
>=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
(MONTH('2003-2004'!$A
>$4:$A$10000)=2),--(TEXT('2003-2004'!$A$4:$A$10000)
="Dave"),'2003-2004'!$E$4:
>$E$10000)
>
>My ideas are better than my knowledge of Excel!
>Better buy a third book.
>
>Joe
>
>
>.
>
0
frank.kabel (11126)
4/28/2004 12:54:46 PM
Jason: I get an #N/A error.

Frank:
My "builder" name is just a named range B4:B10000
And my "servicedby" range is L4:L10000

Joe


"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:54f901c42d1f$fc1817d0$a601280a@phx.gbl...
> Hi
> how are your both names 'builder' and 'servicedby'
> defined. That is which column do they represent. After
> this it should be quite easy.
>
> >-----Original Message-----
> >I have this formula:
> >=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
> (MONTH('2003-2004'!$A
> >$4:$A$10000)=2),'2003-2004'!$E$4:$E$10000)
> >
> >And I only want  this to be true if:
> >
> >
> >{=SUM((builder="m")*(servicedby="Dave")*nc)}
> >
> >I don't think it is as easy as joining two together, but
> I tried:
> >
> >=IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--
> (YEAR('2003-2004'!$A$4
> >:$A$10000)=2004,--(MONTH('2003-2004'!$A$4:$A$10000)=2),
> ('2003-2004'!$E$4:$E$
> >10000))),1))
> >
> >I also tried:
> >
> >=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
> (MONTH('2003-2004'!$A
> >$4:$A$10000)=2),--(TEXT('2003-2004'!$A$4:$A$10000)
> ="Dave"),'2003-2004'!$E$4:
> >$E$10000)
> >
> >My ideas are better than my knowledge of Excel!
> >Better buy a third book.
> >
> >Joe
> >
> >
> >.
> >


0
4/28/2004 1:35:43 PM
I think it is working now.
I had my "builder" and "servicedby" ranged a different lenght from the other
ranges.

Joe

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:54f901c42d1f$fc1817d0$a601280a@phx.gbl...
> Hi
> how are your both names 'builder' and 'servicedby'
> defined. That is which column do they represent. After
> this it should be quite easy.
>
> >-----Original Message-----
> >I have this formula:
> >=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
> (MONTH('2003-2004'!$A
> >$4:$A$10000)=2),'2003-2004'!$E$4:$E$10000)
> >
> >And I only want  this to be true if:
> >
> >
> >{=SUM((builder="m")*(servicedby="Dave")*nc)}
> >
> >I don't think it is as easy as joining two together, but
> I tried:
> >
> >=IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--
> (YEAR('2003-2004'!$A$4
> >:$A$10000)=2004,--(MONTH('2003-2004'!$A$4:$A$10000)=2),
> ('2003-2004'!$E$4:$E$
> >10000))),1))
> >
> >I also tried:
> >
> >=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
> (MONTH('2003-2004'!$A
> >$4:$A$10000)=2),--(TEXT('2003-2004'!$A$4:$A$10000)
> ="Dave"),'2003-2004'!$E$4:
> >$E$10000)
> >
> >My ideas are better than my knowledge of Excel!
> >Better buy a third book.
> >
> >Joe
> >
> >
> >.
> >


0
4/28/2004 1:37:21 PM
Thanks Jason and Frank.
Works perfectly!

Joe

"lunker55" <this_is_not_my_email_address@hotmail.com> wrote in message
news:ej%23iSVSLEHA.3204@TK2MSFTNGP10.phx.gbl...
> I think it is working now.
> I had my "builder" and "servicedby" ranged a different lenght from the
other
> ranges.
>
> Joe
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:54f901c42d1f$fc1817d0$a601280a@phx.gbl...
> > Hi
> > how are your both names 'builder' and 'servicedby'
> > defined. That is which column do they represent. After
> > this it should be quite easy.
> >
> > >-----Original Message-----
> > >I have this formula:
> > >=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
> > (MONTH('2003-2004'!$A
> > >$4:$A$10000)=2),'2003-2004'!$E$4:$E$10000)
> > >
> > >And I only want  this to be true if:
> > >
> > >
> > >{=SUM((builder="m")*(servicedby="Dave")*nc)}
> > >
> > >I don't think it is as easy as joining two together, but
> > I tried:
> > >
> > >=IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--
> > (YEAR('2003-2004'!$A$4
> > >:$A$10000)=2004,--(MONTH('2003-2004'!$A$4:$A$10000)=2),
> > ('2003-2004'!$E$4:$E$
> > >10000))),1))
> > >
> > >I also tried:
> > >
> > >=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--
> > (MONTH('2003-2004'!$A
> > >$4:$A$10000)=2),--(TEXT('2003-2004'!$A$4:$A$10000)
> > ="Dave"),'2003-2004'!$E$4:
> > >$E$10000)
> > >
> > >My ideas are better than my knowledge of Excel!
> > >Better buy a third book.
> > >
> > >Joe
> > >
> > >
> > >.
> > >
>
>


0
4/28/2004 1:45:00 PM
Reply:

Similar Artilces:

Conditional format that higlights differing data on two worksheets
I have a workbook that contains a worksheet for a single week of any given month and in the sheet I have an individual's time reported for each day of the week. I have a second workbook that contains an individual's time reported by each day for the entire month. I would like to compare the two to determine if there is a mismatch and highlight those cells. The logic goes something like this: (1) I need to match person A in column C of workbook1 to the same name in column C in workbook2. (2) I then need to match the date of the month on workbook 1 & 2 for person A in step #1. (3)...

Excel Formulae #4
I've been trying hard to find a formula which would enable me (from one cell) to Add several cells and Subtract the answer from another cell (subtracting items of expenditure from a starting total). I could manage this with Lotus - but Excel does not seem to behave the same. Advice would be much appreciated. Thanks, Ken. One way: Say you wanted to subtract A1, A2, A3, J4 and N5 from L6: =L6-SUM(A1:A3,J4,N5) In article <173d01c3fbd6$3df0f300$a401280a@phx.gbl>, "KenS." <anonymous@discussions.microsoft.com> wrote: > I've been trying hard ...

Viewing / Printing more than two pages at once
I'm working on a publication, and I'd like to get a proper overview of it (balance of pictures etc) However, I can only view two pages at a time. Is it possible to view lots of little pages - say 8-10 at a time? Like you can in Powerpoint. If not, can I print lots of pages on one sheet? In the Print setup I can only get two pages at a time. Only in print preview. On the toolbar there is an icon that you can select to show you multiple pages. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "KMcA" <KMcA@discus...

ODBC Failure In JOIN Query ??? (BUT....)
I have a query that is simply a combination of 3 other queries (joined by 1 common field in each). Each of the 3 "sub queries" will execute fine, Individually.... But....that final query which simply combines them all will display an ODBC failure error. I'd always assumed that if each individual query ran okay....the query which combines them should not get such a data-access related error (since the core data is already being successfully accessed). Has anyone else experienced this and found a solution? Thanks very much -- Message posted via AccessMonster.com http://www.a...

How To Allocating Rent Between Two Categories?
Original Question: [The part I need help with is that the amount in column G has to be allocated to the following accounts in this order Gas, Hydro, Promo FC CAM Mkt CAM Base % Rent I'm just working on allocating the last two, so I'm assuming that there is rent remaining to be allocated from column G after allocating to Gas, Hydro, Promo, FC CAM and Mkt CAM. The remaining balance has to be allocated first to base rent than to % rent on a monthly basis. If some rent is allocated to % rent, and the next months sales are so low that the base rent is fully filled we need to first reduc...

Shorter Formula
Can anyone shorten this formula please. Basically all it does is gives me an average of the figures in Column "W" depending on the number of times that product appears in "R" column =IF(ISERROR(SUM(SUMIF($R$5:$R$9,R62,$W$5:$W$9),SUMIF ($R$22:$R$26,R62,$W$22:$W$26),SUMIF ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF ($R$5:$R$43,R62)),0,SUM(SUMIF ($R$5:$R$9,R62,$W$5:$W$9),SUMIF ($R$22:$R$26,R62,$W$22:$W$26),SUMIF ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF($R$5:$R$43,R62)) thanks Pete I didn't try too hard to analyze your formula, just noted that your ranges and sum_ ranges...

Formula counts incorrectly
Help please. Column K contains dates and blank cells. I would like to count how many of these dates fall within a given date range. For example, in the first week in May. Column K contains these dates. 5/2/2005, 5/3/2005, 5/5/2005, 5/5/2005. I would like to count this as 4 dates within the first week of May. I created this formula but the output is 3. =SUMPRODUCT(--(ECNT!F2:F515>=DATE(2005,5,2)),--(ECNT!F2:F515<=DATE(2005,5,6)),--(ISNUMBER(ECNT!K2:K515))) I assume that it is only counting 5/5/2005 one time? I changed the dates in the formula to check a second 2nd date range (5/9 to ...

Mail Delivery Locally Between Two Exchange servers Locally
Hi, 1) I have two Exchange servers hosting Exchange 2000, Exchange 2003 in the same subnet. 2) two domains are different from each other (DmainA.com.sg, DonainB.com.sg). 3) When these exchange servers send mails, it will go through the SMTP of its own. 4) when DomainA send mail to DomainB or viseversa it will go through the SMTP which results slow delivery of mail. Q:- 1) is there any way i can configure these servers to deliver mail to each other directly. 2) on Exchange 2000 any mail for DomainB must go through any conector,deliver directly or smar...

Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental changes during update by different users. However, if I protect them, any new row creation and deletion are disable. Is there any way resolve it? Especially, when create a row, how can all formatting and formula to create automatically for the new row? Thanks, Scott What version of Excel? "Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message news:%23bsg6kCBGHA.4092@TK2MSFTNGP09.phx.gbl... >I need to protect the formatting & formula in each cell to avoid accidental >changes d...

Is there a way to automatically put the $ sign into the formula?
I want to turn this =A1 into =$A$1. How is the best way of doing this. Is there a shortcut....keyboard stroke to do this?? Hit [F4] several times and watch the results. Micky "hrb_2001" wrote: > I want to turn this =A1 into =$A$1. > How is the best way of doing this. Is there a shortcut....keyboard stroke to > do this?? > Hi, Enter =A1 then tap F4 Repeated taps of F4 scroll through the referencing options -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently an...

two smtp address
Hi, I have a client who has an exchange server and uses outlook 2000 for mail messaging. She has her main email address and an additional alias smtp address. She recieves mail from both aliases and when she sends messages it only sends from her main address. Is there a way to change so she can send emails from her other smtp address. Thanks. Randy Well, if she used Ol2002/2003, she could set up a pop or imap (cam use fake pop server name) with the second SMTP address and choose the account to send on... but not with 2000. There is a 3rd party app that she can install that will allo...

Help writing a formula
I have a huge database that I need to create numbering for each line like this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the number doesn't change each line - sometimes the number will be duplicated on multiple lines because those lines have the same title, I also need to create another cell extending that letter/number combo that will differentiate the 1st cell. So the first cell stays the same if the title is the same. The second cell adds another layer of numbering to differentiate the first cell. See example: (Row1, Col 1-4) AK-1, AK-1-1, Title1...

Re: Could someone please help me with formulas
sorry typo http://www.eaglepi.com/formula/example.xls "No" <no@isp.com> wrote in message news:... > I created a workbook and tried to explain the best I could how everything > should be. you can download the workbook at, > http://www.eaglepi.com/formlua/example.xls > > I really appreciate everyones help on this..... > > > "CLR" <croberts@tampabay.rr.com> wrote in message > news:%235IxtFEHFHA.2936@TK2MSFTNGP15.phx.gbl... > > Maybe in cell J2 you could put the formula =H2+I2, which would give you > the > > sum of the C...

turning off formula bar
I can't remember or find how to turn off the formula bar in Excel 2003? Can someone help please. This is the bar that displays cell contents up top. Thanks! --Randy Starkey Tools>Options>View tab, uncheck Formula bar -- Kind regards, Niek Otten Microsoft MVP - Excel "Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in message news:12kcghhahv0oif4@corp.supernews.com... |I can't remember or find how to turn off the formula bar in Excel 2003? Can | someone help please. This is the bar that displays cell contents up top. | | Thanks! | | --...

Formulas in a criteria area
Can a formula be used in a criteria area when extracting data from a table in Excel 2007? ...

Deleting multiple rows through a formula
So does anyone know a formula that will allow me to delete multiple rows of re-occuring data in a spreadsheet. For example, I have a spreadsheet of 10,000 lines, with multiple mobile numbers on it. I need to only have one of each mobile number and delete the remaining rows where the mobile has been repeated? Mike you may be able to use the advance filter where can select unique records only. select data filter advanced filter select copy to another location then in list range box enter range to filter in the copy to box enter a free column select unique records only then ok then just ...

How do I clear a column of data without clearing the formulas?
I want to zero out a column of data, but not the subtotal formulas. How can I do that easily without going to every cell? Hi Select your column then choose edit / goto / special - constants - ok this will just select the non-formula cells then press the delete key cheers JulieD "EllenSwarts" <EllenSwarts@discussions.microsoft.com> wrote in message news:DD518142-47BC-438D-BE23-8DF1E9F72042@microsoft.com... >I want to zero out a column of data, but not the subtotal formulas. How >can > I do that easily without going to every cell? qlso look at: http://www...

Lookup formula question #2
Hi all, I have a Lookup formula setup to validate what the user enters against a list of stock codes, returning the item costs. Stock codes are A001, A002 etc, B001, B002, etc, D001, D002 etc If I then enter a stockcode starting with letter C, I get the item cost returned from the last letter B stockcode. How can I get it to return P.O.A. This is what I have at present. =IF(C4="Fittings",(LOOKUP(F4,Fittings!$C$1:$C$5019,Fittings!$D$1:$D$5019)),0)*AL4 C4 - Parent stock item description F4 - Stock code AL4 - Quantity Any help - greatly appreciated. -- PeterG ------------...

Formatting formulas
I'm entering a formula into a cell. As I enter the formula, Excel is providing the normal cues, i.e., telling me which argument I should be entering (all well and fine). However, once I'm done, it appears that Excel is interpreting what I entered as text. If I edit the cell, all looks fine. If I check the Format of the cell, it says it's a text. What am I doing wrong? .. . .and the formula is . . . .? -- Russell Dawson Excel Student "jmt" wrote: > I'm entering a formula into a cell. As I enter the formula, Excel is > providing the...

Need help with max and if formula
I need to create a cell entry that displays the name of the salesperson who sold the most cars in the quarter. Use a combination of the “IF” and "MAX" functions to do this. Hint: you will need to use these functions function multiple times in the same formula to do this. Hint: you do not need to "use these functions function multiple times in the same formula to do this." Hint: use a combination of INDEX, MATCH and MAX. Biff "jeremiahw23" <jeremiahw23@discussions.microsoft.com> wrote in message news:789DA4A4-9515-4EEF-BF12-606CAAFEBDF9@microsoft.co...

formula help
I have a cell with this formula: {=INDEX(B:B,MAX((B1:B368<>0)*ROW(INDIRECT("1:368"))))/(316+COUNTIF(D3:D368," >0"))} I don't remember how it was made up (I got help on this forum), but now I need to update it by changing the 316 summed value in the last expression to 681. When I do that, the starting and ending braces disappear, and the cell containing this formula returns a #VALUE! message. How to fix? Thanks. -- Bill Hi enter this formula with CTRL+SHIFT+ENTER (to create an array formula) -- Regards Frank Kabel Frankfurt, Germany "Bill H.&quo...

ADO, Recordset and two Access mdbs?
I have two Access database, and I want copy records from DB1 to DB2. If have records from DB1mdb in ADODB.recordset, could I insert records to DB2.mdb (Same table structure/name) directly (whitout creating insert -sql statement)? How? I have two ADODB.connections, ofcourse. I'm using VB6 and ADO... "Major" <lievonen@jyu.fi.HALOOOOOOOO> wrote in message <news:eN9basLuDHA.2304@tk2msftngp13.phx.gbl>... > I have two Access database, > and I want copy records from DB1 to DB2. > > If have records from DB1mdb in ADODB.recordset, > could I insert record...

Expanded formula bar
Often, I lose track of brackets in long formulas. If I could expand the view in the formula bar it may make it easier to create and edit long formulas: C7+C28 C26*C12 C30/( )+( )^3 =SQRT( ) Is there an automatic way to show the formula bar like this? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in t...

Difference between two times in seconds
hi i need a favour regarding finding out the time spent between two times in seconds. I need the current time like 12:35:45 PM in a cell A1 and after some time put time in cell A2 as 12:37:50 PM. I need the shortcut formula for putting in a cells (A1&A2) so that it should come in Hr: Min: Sec format. And after that I need the difference of this two times in Seconds (125 seconds above) in cell A3. hi use a custom format in A3. [ss] this will keep the time from rolling over to minutes and total the seconds. regards FSt1 "Radhakant Panigrahi" wrote: ...

time formula #2
I am trying to calculate in and out time for a time sheet spreadsheet. Example: 9:00 am (in time), 5:00 pm (out time). I devised a formula: =IF(D7<C7,((C7-D7)*24)+((C7+D7)*24)*2,(D7-C7)*24) It works for all senarios except if you begin with PM and end with AM. Example: 5:00 pm (in time), 1:00 am (out time) Can anyone help me!! I've done something similar for Surgery in and out time for my hospital. Our times never cross a day though. Understand that time is saved as a faction of a day: a date/time raw looks like 1000.0001 everything to the left of the decimal is date info, e...