find a date on sheet 2 and count text in that column

I'm lookung for the formula to use that if I was under one date on one page, 
to look what date im under go to the 2nd page, find that date then count the 
text specified under that date.  My pages are set up as follows:

sheet 1

        A                      B                  C                   D      
             E
1|                            1-oct             2-oct              3-oct     
         4-oct
2|   john                 text 1           text 1             text 2         
     text 1
3|   jack                  text 1           text 2            text 1         
      text 1
4|   jeff                   text 2           text 2            text 1        
        text 2


sheet 2
        
      A                       B                  C                    D      
              E
1|                         1-oct              2-oct               3-oct      
        4-oct
2|   text 1                 2                  1                      2      
             2
3|   text 2                 1                  2                      1      
             1


For instance, what formula would I use if I wanted to search for "1-oct" on 
sheet 1 for "text 1" and have the total in cell under "1-oct" on sheet 2 in 
line with "text 1" row?  Keep in mind my dates will change so I want to be 
able to reference to the text that is in the cell above where my total will 
be. 
0
jtinne (33)
10/4/2004 5:47:03 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
362 Views

Similar Articles

[PageSpeed] 26

=SUMPRODUCT((Sheet2!$B$1:$E$1=B$1)*(Sheet2!$A$2:$A$10=B2),Sheet2!$B$2:$E$10)

-- 

HTH

RP

"jtinne" <jtinne@discussions.microsoft.com> wrote in message
news:ADD085F9-B470-4F48-9B99-DF338A101DCF@microsoft.com...
> I'm lookung for the formula to use that if I was under one date on one
page,
> to look what date im under go to the 2nd page, find that date then count
the
> text specified under that date.  My pages are set up as follows:
>
> sheet 1
>
>         A                      B                  C                   D
>              E
> 1|                            1-oct             2-oct              3-oct
>          4-oct
> 2|   john                 text 1           text 1             text 2
>      text 1
> 3|   jack                  text 1           text 2            text 1
>       text 1
> 4|   jeff                   text 2           text 2            text 1
>         text 2
>
>
> sheet 2
>
>       A                       B                  C                    D
>               E
> 1|                         1-oct              2-oct               3-oct
>         4-oct
> 2|   text 1                 2                  1                      2
>              2
> 3|   text 2                 1                  2                      1
>              1
>
>
> For instance, what formula would I use if I wanted to search for "1-oct"
on
> sheet 1 for "text 1" and have the total in cell under "1-oct" on sheet 2
in
> line with "text 1" row?  Keep in mind my dates will change so I want to be
> able to reference to the text that is in the cell above where my total
will
> be.


0
bob.phillips1 (6510)
10/4/2004 6:09:43 PM
I want exel to find what column that date is in on sheet 1, then search that 
column for that specified text, then count how many times that text appears.

"Bob Phillips" wrote:

> =SUMPRODUCT((Sheet2!$B$1:$E$1=B$1)*(Sheet2!$A$2:$A$10=B2),Sheet2!$B$2:$E$10)
> 
> -- 
> 
> HTH
> 
> RP
> 
> "jtinne" <jtinne@discussions.microsoft.com> wrote in message
> news:ADD085F9-B470-4F48-9B99-DF338A101DCF@microsoft.com...
> > I'm lookung for the formula to use that if I was under one date on one
> page,
> > to look what date im under go to the 2nd page, find that date then count
> the
> > text specified under that date.  My pages are set up as follows:
> >
> > sheet 1
> >
> >         A                      B                  C                   D
> >              E
> > 1|                            1-oct             2-oct              3-oct
> >          4-oct
> > 2|   john                 text 1           text 1             text 2
> >      text 1
> > 3|   jack                  text 1           text 2            text 1
> >       text 1
> > 4|   jeff                   text 2           text 2            text 1
> >         text 2
> >
> >
> > sheet 2
> >
> >       A                       B                  C                    D
> >               E
> > 1|                         1-oct              2-oct               3-oct
> >         4-oct
> > 2|   text 1                 2                  1                      2
> >              2
> > 3|   text 2                 1                  2                      1
> >              1
> >
> >
> > For instance, what formula would I use if I wanted to search for "1-oct"
> on
> > sheet 1 for "text 1" and have the total in cell under "1-oct" on sheet 2
> in
> > line with "text 1" row?  Keep in mind my dates will change so I want to be
> > able to reference to the text that is in the cell above where my total
> will
> > be.
> 
> 
> 
0
jtinne (33)
10/4/2004 6:29:04 PM
Enter the following formula in B2 of Sheet2 and copy across and down:

=SUMPRODUCT((Sheet1!$B$2:$E$4=Sheet2!$A2)*(Sheet1!$B$1:$E$1=Sheet2!B$1))

Adjust the range accordingly.

Hope this helps!

In article <ADD085F9-B470-4F48-9B99-DF338A101DCF@microsoft.com>,
 jtinne <jtinne@discussions.microsoft.com> wrote:

> I'm lookung for the formula to use that if I was under one date on one page, 
> to look what date im under go to the 2nd page, find that date then count the 
> text specified under that date.  My pages are set up as follows:
> 
> sheet 1
> 
>         A                      B                  C                   D      
>              E
> 1|                            1-oct             2-oct              3-oct     
>          4-oct
> 2|   john                 text 1           text 1             text 2         
>      text 1
> 3|   jack                  text 1           text 2            text 1         
>       text 1
> 4|   jeff                   text 2           text 2            text 1        
>         text 2
> 
> 
> sheet 2
>         
>       A                       B                  C                    D      
>               E
> 1|                         1-oct              2-oct               3-oct      
>         4-oct
> 2|   text 1                 2                  1                      2      
>              2
> 3|   text 2                 1                  2                      1      
>              1
> 
> 
> For instance, what formula would I use if I wanted to search for "1-oct" on 
> sheet 1 for "text 1" and have the total in cell under "1-oct" on sheet 2 in 
> line with "text 1" row?  Keep in mind my dates will change so I want to be 
> able to reference to the text that is in the cell above where my total will 
> be.
0
domenic22 (716)
10/4/2004 8:02:32 PM
Hi
in B2 of your second sheet enter the formula:
=COUNTIF(OFFSET('sheet1'!$A$1:$A$100,0,MATCH(B$1,'sheet1'!$A$1:$E$1,0))
,$A2)
and copy this formula across

--
Regards
Frank Kabel
Frankfurt, Germany


jtinne wrote:
> I'm lookung for the formula to use that if I was under one date on
> one page, to look what date im under go to the 2nd page, find that
> date then count the text specified under that date.  My pages are set
> up as follows:
>
> sheet 1
>
>         A                      B                  C
>              D E
> 1|                            1-oct             2-oct
>          3-oct 4-oct
> 2|   john                 text 1           text 1             text 2
>      text 1
> 3|   jack                  text 1           text 2            text 1
>       text 1
> 4|   jeff                   text 2           text 2            text 1
>         text 2
>
>
> sheet 2
>
>       A                       B                  C
>               D E
> 1|                         1-oct              2-oct
>         3-oct 4-oct
> 2|   text 1                 2                  1
>              2 2
> 3|   text 2                 1                  2
>              1 1
>
>
> For instance, what formula would I use if I wanted to search for
> "1-oct" on sheet 1 for "text 1" and have the total in cell under
> "1-oct" on sheet 2 in line with "text 1" row?  Keep in mind my dates
> will change so I want to be able to reference to the text that is in
> the cell above where my total will be.

0
frank.kabel (11126)
10/4/2004 8:06:41 PM
Reply:

Similar Artilces:

Highlighting cells #2
Excel 2007 sp1 with vista 32 business sp1 Background: When you highlight multiple non-contigious cells (i.e. hold the control key down and select several cells that are not connected) Excel highlights the selected cells in light blue. Problem: This light blue does not show at all on many projectors. In some case you can adjust the color temp and this helps. We do hundreds of presentation in North America each year and can not control what projection equipment we will be using. Requested solution: I would change the light blue background that is applied to selected cells to some other colo...

Store Operations crashes when viewing journal (F4) on register 2.
We have two registers, register 1 is the main machine where the database is located and register 2 is networked into it. Whenever you try to view the journal (F4) on register 2, the program crashes and gives this message just before closing: Run-time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from its clients. Does anyone have any suggestions as to what the problem may be? Any help would be greatly appreciated. Install a default windows printer on register 2 - even if it's just something like the print to fax or MS Office Document...

Adding profit to a sread sheet
Hi there I'm working on an extensive pricing catalogue in EXCEL. I need to write a formula that adds a profit to a cell, but if the profit added is less than $100, then it must add $100 instead. Here's what I have so far........ =SUM(C7*A1) For arguments sake I have a cell A1 which is a multiplier (1.3 for a 30% profit). And C7 is the wholesale price that I enter in. (for the sake of the exercise lets use a value of $80) Cell D7 is the total (where the formula will reside). So currently D7 would show $104, but because it is less than $100 profit (ie $24), I need it to be $180. ...

Rollup 2 and Office 2007
I already have Roll Up 1 and the Office 2007 compatibility patch installed. If I now install roll up 2 will I have to reinstall the office 2007 compatibility? yes -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "LLoyd" wrote: > I already have Roll Up 1 and the Office 2007 compatibility patch installed. > If I now install roll up 2 will I have to reinstall the office 2007 > compatibility? ...

Outlook 2003 keeps crashing #2
I get this message when I look in System information. Outlook keeps crashing quite often. I cannot for example delete two messages without outlook crashing. My colleagues do not have this problem. We are using the Microsoft mail exhange server. Faulting application outlook.exe, version 11.0.5510.0, stamp 3f1380f0, faulting module mso.dll, version 11.0.5606.0, stamp 3f334cce, debug? 0, fault address 0x003b6e16. Does anyone has a solution?=20 Best regards/ Bj=F6rn 1) I would definitely apply Service Pack 2for Office 2003 + applicable critical security updates. 2) Have you tried starting O...

Counting number of X's in a column
I need to sum up the number of "Xs" in a column. I would be extremely grateful if someone could help me with the formula. I cannot seem to get one to work. We are using Excel 2003. Thank you. -- Susie Harris Operations Manager Environmental Education Association of Washington =COUNTIF(A:A,"X") -- HTH RP (remove nothere from the email address if mailing direct) "Susie Harris" <SusieHarris@discussions.microsoft.com> wrote in message news:FFA0FB17-C922-48A8-B3C3-0D487C1E6F6A@microsoft.com... > I need to sum up the number of "Xs" in a col...

Microsoft POS #2
I have a group of customers that I am working on setting up A bundled POS system for. I have looked at RMS and Microsoft POS. The customer's are small bulk food stores. They package all of the bulk food in various containers and weigh it with a Hobart scale producing a label with an system 2 price enbedded barcode. These are UPC A formats. They are for the most part simple single store installs and donot require the complex RMS system. I have setup the Microsoft POS version 2.0 and found it will work perfect for these customers. The only problem when you programed the system you l...

Lookup based on criteria in 2 columns
Hi, I am trying to use a vlookup or other function to return the value in the amount column based on the location and date. Here is a sample of my data: Location Date Amount 101 9/15/8 10 101 9/16/8 20 101 9/17/8 15 102 9/15/8 50 102 9/16/8 75 102 9/17/8 67 For example if I wanted to return the amount for location 102 on 9/15/8, what formula would I use? I tried using variations of vlookups but had no luck. Thanks, =SUMPRODUCT(--(A2:A50=102),--(B2:B50=DATE(2008,9,15)),C2:C50) -- Regards, Peo Sjo...

Trying to find out which numbers occure most often
I�m giving exams to students and want to see if the students are missin the same questions in every class. I have a simple spreadsheet tha has me enter the question number into the column each time a questio is missed. The MODE function can tell me which number is occurrin most often. However, I need to see if there is any other numbers tha are occurring often as well. Is there a command that will allow me to look at the second mos entered number? For example, if I have 5,3,3,2,3,4,3,4,1,3,4, my mod would be 3 as that�s the number that repeats the most. However, happens 3 times and that...

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

how do I automatically update a text box in a chart?
Hi, You can link the textbox to a cell. When you update the cell the textbox should reflect this. Select the textbox border and then in the formula bar enter the complete cell reference, for example, =Sheet1!A1 Cheers Andy Kath wrote: -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Lookup text with multiple search criteria
I know how to look up data and text using vlookup and hlookup, what I' like to know is whether there is a way of looking up data based on mor than 1 search criteria; for example If I wanted to look up an item in a 4 column database, I'd us something like this: =VLOOKUP("Apples",A2:D10,4,false) But that only looks for Apples. Say I wanted to lookup data based on subcategory of Apples, e.g. colour. If it was a number, I could use SUMPRODUCT and (assuming named range were in use) do it like this: =SUMPRODUCT((Fruit="Apples)*(Colour="Red")*(Total)) But obvio...

Find prev record on other requeried form
Using access 2003. I have a main form for name and address info. A subform shows limited info on client placement. Click a command button on the main form and user opens another form for full screen entry of placement data. That form opens showing the client selected on the initial main form. When the full screen placement form closes, it requeries the original main form with the placement subform to show the added placement. It goes to the first record in the table though. It does not show the original client whose placement was just added. How do I set the mai...

2 register ruinning together on one z report
Is this possible, to have two registers running on one z report ? We have a small store and would like to setup a second register as a backup, temporary addition ( during rush hours) Since we are a small show i do not need to have a separate register reports. Can two registers ring up all sales together as one ? How can i set this up ? No, you can't do that. You might be able to use the Register Analysis report available in SO Manager Utilities/Crystal Reports to get something close to a single Z though. Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.c...

Please ignore #2
Testing a posting problem -- Jim Bunton ...

dates #5
Is there a formula to ad a numeric value to the name of a month. I am running a query in excel (pulling from an external source) and would like to sort as teh table refreshes by month and year. I have code to to do the refresh and the sort, but it sorts in alphabetical order. I was thinking a formula could place a numeric value to the month (1 = Jan, 2 = Feb, etc) and I can sort based on that. Is there an easy formula to do this. Thanks in advance Your easy formula could be: =MATCH(A1,{"Jan","Feb","Mar","Apr","May","Jun"...

matching contents #2
E1 thru E10 contains sequentially-sorted data. B1 thru B6 contains sequentially-sorted data which matches SOME (but not ALL) of the data in Col A. For example. B E 1 1 1 2 3 2 3 4 3 4 5 4 5 6 5 6 10 6 7 7 8 8 9 9 10 10 I need to move the data in the cells in Col B next to the cells in Col E that have the same contents. For example: B E 1 1 1 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 8 8 9 9 10 10 10 Besides dr...

Calculating age in a label or text box on a form
Hello All, I have a text box for birthdate for a user to enter the birthdate and I want to the age for the user to be automatically calcuated and displayed in another label called "Age." What formula can I use? Missy ...

Font dosen't revert back to original after typing in date
Example I n my text I type I have December 28th The th automatically reduces to a much smaller font and once you hit the sapce bar to continue it reverts back to the larger font. It only does this some of times, why is this being so inconsistent?. I refortmated my comp. last week and when I reinstalled office I was hoping this inconsistent problem would have been resloved, but no 2nd question please. When I post a question in "discussion group" and check the box "Notify me of Replies" I never receice a reply to my question I then have to go back to ...

Convert date
How can I convert a date in this format (mm-dd-yyyy) to a Julian-date (i.e., YYYYDDD)? For example: 09-13-2003 to 2003256 Hi see: http://www.cpearson.com/excel/jdates.htm -- Regards Frank Kabel Frankfurt, Germany gary wrote: > How can I convert a date in this format (mm-dd-yyyy) to a > Julian-date (i.e., YYYYDDD)? > > For example: 09-13-2003 to 2003256 On Wed, 8 Sep 2004 10:24:57 -0700, "gary" <anonymous@discussions.microsoft.com> wrote: >How can I convert a date in this format (mm-dd-yyyy) to a >Julian-date (i.e., YYYYDDD)? > >For exa...

formating date from general2 custom
I have an input xls file with the date but like this: Wed May 14 00:00:00 GMT-05:00 2008 I need to have just following format dd/mm/yyyy I tried to format it using Format->Cells->Date or Format->Cells- >Custom but this is failing. Nothing changed. Any ideas? Thanks for the help. Vlad <vladimir.vaynshtok@gmail.com> wrote in message news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com... >I have an input xls file with the date but like this: > > Wed May 14 00:00:00 GMT-05:00 2008 > > I need to have just following format > > d...

Outlook Express 5 #2
On loading, error message "MSIMN caused an invalid page fault in module DIRECTDB.dll at 017f:7dbf3070.." Appeared to coincide with display config change? Ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is the link for the right forum http://communities.microsoft.com/newsgroups/default.asp?icp=InternetExplorer Good Luck! -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tip of...

CRM 1.2 12-02-05
I have installed MS CRM 1.2 on a SBS 2003 server. Problem is I can only start the application if I log onto the server usind the server administrator account. If I try to access using a domain users account I just get a page cannot be displayed error, could somebody please help... Have you added the appropriate domain users as crm users using the deployment manager? "Redders" <Redders@discussions.microsoft.com> wrote in message news:D80FF8B4-78F1-4334-ACB5-2F772F0CF3B6@microsoft.com... > I have installed MS CRM 1.2 on a SBS 2003 server. Problem is I can only start >...

Workbook_Open Help #2
Hello Frank, Here's the code Private Sub Workbook_Open() ' Call Unhide_Sheets ' Sheets("CONTROLPANEL").Select Range("A1").Select Call Splash End Su -- Fabl ----------------------------------------------------------------------- Fable's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=218 View this thread: http://www.excelforum.com/showthread.php?threadid=26252 Hi and there's no application.screenupdating=false statement in your unhide procedure? -- Regards Frank Kabel Frankfurt, Germany "Fable" <Fable.1d07w...

Sum column values that contains text &/or dates
Column D3 thru D43 contain a mixture of values & dates. For example: D3 = 16,026.76 D4 = 11-18-05 D5 = blank D6 = 6,855.34 D7= 11-03-05 D8 = blank D9 = 5,270.00 D10 = 11-02-05 Need sum to equal 28,152.10 (total only values from entry or formulas) Thanks so much. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=489114 If your dates are text strings which say "11-18-05"...