HELP!!! Can't get forumla figured out!

Hello,

This is my first posting to this forum, and I haven't been able to
search using the right criteria (not even sure what I would search
under) to find what I need so I thought I would post a thread with the
question.

I have an access query that gives me the results in a spreadsheet
layout, with 2 worksheets on it.

I need to create a forumla that will look at the data on the 2nd sheet
and compare the values in 2 particular columns with the value in one
cell on the 1st sheet and populate a different cell on the 1st sheet
with a count of entries from the 2nd sheet.


If Sheet B, Column 1 (2:897 data range) AND Sheet B Column 2 (also
2:897 data range) = Sheet A Cell D, count those entries on Sheet B
Column 2 (2:897 data range) equal to the data in Sheet A Cell E, and
populate Sheet A Cell F with the number.

Is such a forumla possible?

Please help me ASAP, as I'm ok with the simpler formulas, but these
tougher ones are beyond my ability without some help.

I need to know so that I can get my boss off my case by either getting
the formula figured out or by telling him that its not possible.

Thanks for any and all help in advance.

-JT 

:confused:  :confused:


-- 
JTKrupa
------------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27759
View this thread: http://www.excelforum.com/showthread.php?threadid=472703

0
10/3/2005 5:49:16 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
426 Views

Similar Articles

[PageSpeed] 3

It sounds like you're looking for a combination of SUMIF and VLOOKUP
formulas.


-- 
shternm
------------------------------------------------------------------------
shternm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=858
View this thread: http://www.excelforum.com/showthread.php?threadid=472703

0
10/3/2005 6:04:46 PM
how do I combine these two functions?


-- 
JTKrupa
------------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27759
View this thread: http://www.excelforum.com/showthread.php?threadid=472703

0
10/3/2005 6:30:19 PM
It sounds like you have criteria:

SheetB!A2:A897 = value in D
SheetB!B2:B897 = value in D
SheetB!B2:B897 = value in E (same as above?)

If that's the criteria, value in D must equal value in E in order to
count.  Can you explain more?


-- 
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7094
View this thread: http://www.excelforum.com/showthread.php?threadid=472703

0
10/3/2005 8:16:39 PM
Hi

If I understand you correctly, then in cell F1 on SheetA
=SUMPRODUCT(--(SheetB!$A$2:$A$897=D1),--(SheetB!$B$2:$B$897=D1)) + 
SUMPRODUCT(--(SheetB!$B$2:$B$897=E1))

If this isn't correct, post back with more details


Regards

Roger Govier



JTKrupa wrote:

>how do I combine these two functions?
>
>
>  
>
0
roger1272 (620)
10/4/2005 9:48:22 AM
I appreciate all your help, but after further investigation on my par
as to just what my supervisor is asking to be done, I'll have to do i
in Access, which I can do just fine.  The current Access generate
results do not give enough information to do what I had in mind wit
Excel.  The query only returns ~900 rows, and the summary of the repor
I'm trying to automate has to account for >40,000 lines.

I need to do some serious query modification from the way the origina
author wrote it.

But thanks anyway.  I do appreciate the input.  I'm sure I'll b
posting again at some point in the near future.  I'm glad to have foun
such a valuable resource for this type of experience and knowledge.

Thanks everyone!!

-JT

:cool:  :cool

--
JTKrup
-----------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2775
View this thread: http://www.excelforum.com/showthread.php?threadid=47270

0
10/4/2005 5:47:59 PM
I'm back again...this stupid thing is still not working right...

what I need is this:

IF:

Sheet B range A2:A897 = Sheet A cell A9
and
Sheet B range E2:E897 = Sheet A cell C8

THEN,

Count the text values in Sheet B range A2:A897 that satisfy thes
criteria.

OTHERWISE, display "0"

Any suggestions?

I've been playing with the basics, SUMIF, SUM, IF, COUNTIF and haven'
been able to get a working formula.

I really need to get an Excel 2003 for Dummies book through work...an
suggestions on a particular book as well?

Thanks!!

-J

--
JTKrup
-----------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2775
View this thread: http://www.excelforum.com/showthread.php?threadid=47270

0
10/13/2005 8:16:49 PM
I don't want to "beat a dead horse" but to make sure everyone is on th
same page...

For each row on Sheet B (2:897) I need to have it look at the values i
columns B and E, and compare them with the values on Sheet A (Cell A
for Sheet B column B, and Cell C8 for Sheet B column E).

If all is equal or true, then I need to count the number of cells i
Sheet B column B that equal the data in Sheet A cell A9.

I don't know how else to describe it clearly...other than i
person...I'm trying to give as much info as I can...I apologize i
anyone is offended by my "lowly" description (i.e. trying to explain i
to a non-Excel user) but I have found that sometimes breaking it down t
the absolute basics makes sure we all understand on the master level o
what is trying to be accomplished.

Thanks again!

-JT :

--
JTKrup
-----------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2775
View this thread: http://www.excelforum.com/showthread.php?threadid=47270

0
10/13/2005 8:22:14 PM
Hi
I'm not an expert but maybe a combination of IF and AND:
=IF(AND(B2=sheetA!$A$9,E2=sheetA!$C$8),COUNTIF(sheetB!$B$2:$B$897,sheetA!$A$9),0)

HTH
JG


-- 
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261
View this thread: http://www.excelforum.com/showthread.php?threadid=472703

0
10/13/2005 9:13:34 PM
Reply:

Similar Artilces:

How to get the key sent by RDP control sendkeys() method.
Hi all, I want to make a App runing in the RDP session, and it can receive the message sent by a RDP control SendKeys() method. I have made a successful test that the CMD.exe can receive SendKeys message. Does anyone give me any help? Thanks a lot. ec ...

help sos #2
error 421 smtp service is not available ...

Missing Report Manager help file
Greetings: I run Windows XP home and Office XP Professional. Sometime last year I installed Report Manager add-in for Excel. In March 2005 I had to do a reinstall. Since then, when I click on View>Report>Manager>help I get a message stating that the XLMAIN8.HLP file cannot be found. The utility is checked in the Add-On dialog box. As I still have the download in the My Downloads folder I have even tried reinstalling the utility again but still the help file remains lost somewhere. Can someone offer some advice on what to do next? Where could the file have gone? How can I get it back...

how to get rid of read only
hi When I copy my files to CD and use the CD in another PC, the files ar available as 'read only'. Is there a way to get rid of this problem? thanks Tom -- Message posted from http://www.ExcelForum.com You cannot save direct to a CD therefore all files on CD are read only. --- Message posted from http://www.ExcelForum.com/ Smile, when you put the files on the other computer you can right click on them, select them all at once, and choose properties and uncheck read only -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so...

Howto get standard SystemInfo dialog
Hello, does anyone know how to call the 'Microsoft System Information' dialog from mfc? The same dialog you get e.g. at the Microsoft Word -> About -> System info button. Thanks in advance, Arno Try a ShellExecute[Ex]() / CreateProcess() of msinfo32.exe -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Arno" <Arno@discussions.microsoft.com> wrote in message news:AA524CFD-AF69-4606-888C-D3BF60BEA4DB@microsoft.com... > Hello, > does anyone know how to call the 'Microsoft System Information' dialog from > mfc? > T...

how to get the row and col count of a TMGrid using sendmessage?
I have a 3rd party application which uses TMGrid, I want to get the row and col count of this TMGrid using SendMessage. The Parent Control is a TTabSheet. Anyone know how to do it? I tried the LVM_ message, but failed. Hi, try to find out what messages this control can handle, e.g. with Spy++. Just give it a try,... Regards Kerem -- ----------------------- Beste Gr�sse / Best regards / Votre bien devoue Kerem G�mr�kc� Microsoft Live Space: http://kerem-g.spaces.live.com/ Latest Open-Source Projects: http://entwicklung.junetz.de ----------------------- "This reply is provided as ...

0x8004210A smtp errors on outlook 2003
Hi all, I've read just about everything I can lay my hands on with this problem and am rapidly getting nowhere...... heres what I've got : multiple new, freshly build ol2003 installs on new xp pro sp1 machines. Some of the machines intermittently get this error - sometimes all the time, other times seldom. One or two machines have never had it. All machines have been through an identical build process. heres what I think its not: - not the MTU problem mentioned elsewhere - have tried the blackhole and pmtu changes and they dont help. - not anything to do with antivirus - running ...

Help!! think I messed this up!!
Hey need your help!! We have 2 domains, 1 2000 and 1 4.0 domain. We have 2 2000 exchange servers running in the 2000 domain and 1 exchange 5.5 box running in the 4.0 domain. We have ADC running. I moved the 5.5 user to the 2k exchange box like your supposed to. Management doesn't want to move the user into the 2000 domain. Nice huh? Well they could open their mailbox on the first 2000 exchange server. But it would act like it was trying to connect to the 2nd 2000 exhange box becuase it was coming up with a logon box with the 2nd exchange server name. So I disabled the ADC and mov...

Need help on this....thanks
I have an Excel worksheet which stored a survey outcome of over 1200 members. There are over 30 questions in the survey and the first column captured the member ID. The problem is 3 of the survey questions that allow multiple answers, instead the answer for those three questions are captured in seperate column, the answers are stored in a single column and it makes it very difficult to analysis those result. As a result I created addittional columns depending on the total number of the answers available for that question and assign the result manually to that column. It is very time consuming ...

help with cell referencing in named ranges
I have a named range covering about 30 columns of data. I'd like to lookup within the named range based on values in the 1st column, and a specific date which is the label on top of each of the other 29 columns. I have typed in a new column number as necessary, but I'd like to automate the column number by looking at a separate cell which gives the date. Ex: Dates across the top of the named range are 1/1/90, 1/1/91, etc thru 1/1/2003(in sheet1). Lets say I have a cell in sheet2(d1)that has the date 1/1/2003. In that same sheet starting in cell a1 I have a value which ...

inbox.dbx infected with netsky !!! HELP!!!
I have one windows xp pro laptop with outlook express the inbox.dbx file seems corrupted because months of email just suddenly went missing for no reason I suspected that it is corrupted I installed easyrecovery pro and ran the emailrepair function to recover the inbox.dbx file I successfully recovered most of the mails But when the easyrecovery emailrepair is just about to write the repaired inbox.dbx file into the hard drive, my symantec antivirus corporate edition complains that the repaired inbox.dbx file is infected with the NETSKY virus .... and deletes the inbox.dbx file because it...

Pie Chart Help
Can anyone help? I have a spreadsheet to which I have one column that has different letters in, I need to create a pie chart that shows the amount of the same letter (i.e. 'P's in blue, 'F's in red etc) just so I can show how many of one letter there is? Any ideas and please explain fully as I am quite a new user Add a heading to the column of letters then you could create a pivot table to summarize the data, and create a pie chart from the pivot table results. There are instructions for pivot tables in Excel's Help, and Jon Peltier has information and links: ht...

Where do I get codes from?
Hi, I'm a new user of MS Access and I need to find out where to find the codes for formulate reports in the system. Most of my report are very basic, but I'm not able to find where to go to enter any fuctions or sums for fields....Can someone advise me on this...also, does anyone know how to tell the system to calculate a blank field. For example, if I want to run a report that shows no completion dates or the fields are blank, how would I do that? I know this is very basic, but I haven't used MS Access in years and I'm not sure where to go to retrieve info.... A common...

screen is showing slides in backward order .. . help!
Hello I have a pptx presentation with six slides. For some reason, everything is defualting the the right (the bullets come out to the right of the words, the slides print as handouts in backwards order, the outline pane is on the right hand side of the slide pane). THis is only happening for this presentation. Others I create are the typical way PP usually works. PLEASE HELP! I wish I could upload it here but am unable to do so. See your other post -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_t...

HELP need index of the actual selected point
Hi, I need help. I must get the index of a the currently selectd point in the chart. I can't use the Mouseup event. It will be better to determinate the x and y value from the actual selected point but the index will also be good. Please help! thx Jens You need to use an old-style XLM command for this, as Stephen Bullen pointed out in his post 14-Jan-2001: Sub WhichPoint() Dim sPoint As String Dim iSeries As Integer, iPoint As Integer sPoint = ExecuteExcel4Macro("SELECTION()") If sPoint Like "S*P*" Then iSeries = Val(Mid$(sPoint, 2...

How to get the type code for a custom entity ? MSCRM v3
I created a new entity Called PRITem. When using theSiteMap.XML I need to specifity the EntityType to show it. Ity appears (Automatically) in the workspace. But i can't use it in the SiteMap.XML because i can't find the number. For example: <SubArea Id="nav_activities" EntityType="4200" Icon="/_imgs/ico_16_4212.gif" Url="/Workplace/home_activities.aspx" /> <SubArea Id="nav_calendar" Icon="/_imgs/area/18_calendar.gif" ResourceId="Homepage_Calendar" Url="/workplace/home_calendar.aspx" Clie...

POP email multiple duplicate downloads HELP!
Hi, ISP is SBC Global. I just installed Outlook 2003 yesterday and it keeps downloading ALL the mail I have in the SBC web inbox, even though I"ve marked them as "read" online, multiple times. Duplicate finder [bought and installed] doesn't recognize most of them as dupes. I've done "detect & repair," [got error 1311 source file not found; C:\msocache\allusers\90000409-60000-11D3-8CFE-0150048383C9\ZS561.CAB. Couldn't find that in my installation disk, either.] SBC says they do not support Outlook 2003 -- "it's too new." Superviso...

Form control events get broken.
My colleagues and I have a problem that occurs while designing access forms and it isn't helped by the fact that our applications have a lot of buttons on their forms. The best way I can describe the problem is to explain how to repeat the problematic behavior. 1) Create a blank form and stick a button on it and connect it to some code say: Private Sub Command6_Click() MsgBox ("Hello World") End Sub 2) Change the form to design view. Right Click the button then do edit cut, edit paste from the main menu. Right click the form title bar and choose form view. Click the butt...

Help with System Attendant
Hi all: My situation is this. We had a single system running AD/exchange (windows and exchange 2000). Not ideal, I know :) The systems' mft became corrupted, and I spent a long night bringing up a 2nd exchange server. I was able to bring up the old server, move mailboxes, and have everyone up and running before the next biz day. I've since installed an AD controller on another machine, as the original (problem) server could die any day. I found where to set the 2nd server as a global catalog server, and thought I might have been done. That is, until I tried adding new use...

find&select button,help please?
Hi everybody, i'm using excel 2007,when i press the find&select button it dosen't sow me the cell i'm looking for,it has been disabled,although when i look the rows one by one i can find it,how can i able the find&select. i'd appreciate if u'd let me know,cuz i'm in trouble here ...

Help with rules not working #2
Hi: I'm continuing to experience problems with Rules working. This time, I have Outlook 2003 connected to Exchange 2000 Server, set up to run on a PC (Win XP pro). I'm connected to that PC with a remote desktop session that is always open. None of the rules work unless I run them manually (even rules that are server side). Does anyone know if a command line switch (or anything else) will work to ensure that rules actually run automatically? Thanks for any suggestions -- Dab Cut off: yourhead to respond ...

Help] How to merge two charts?
1. There are two charts M and N. Column A of chart M means part-number (numbers and letters), Column B of chart M means part-name (in Japanese); Column A of chart N means part-number (numbers and letters), Column B of chart N means part-name (in English); 2. Column M contains much more parts than Column N, which means N belongs to M; But M and N obey exactly the same part-number naming rule; 3. While most people can't Japanese but English, so it is planned to add the English part-names from Column N into Column M, just as remarks to corresponding Japanese part-names, meaning ...

please help OE & OL between 2 users
I am admin account and run Outlook. Girlfriend is limited account and runs OE. When she right clicks pics and the like to send to mail recepient, it opens an Outlook new message window. Now, these messages never get sent as she doesnt use Outlook as her email program. They simply accumulate in the Outlook outbox and never get sent. I cant set OE as her default email as it wont let me do that (I guess since Im admin and I use it). What should be done? Ive tried this all but the registry change http://support.microsoft.com/?kbid=306098 and it wont take. Whats the best way to do...

Oy, I need some help!!
Hi, Here is the problem: I use outlook express my wife uses outlook 2000 on a separate computer. I tried to combine the 2 computers into one. I copied my contacts from OE, went to the other computer and imported them in OE. Everything looked fine. When I went to Outlook, all the contacts were the ones in OE. I don't know what happened to her contacts. Is there any way to find and recover them? Do I need specific data recovery software? Is there any available on line? Please help... she is gonna kill me...... then divorce me!!!! Usually if Outlook and Outlook Express are set to s...

Help with memory leak (possibly in CArray)
Hi all, I've an App that takes about 4 hours to run before it throws the error I'm trying to fix, so I don't really fancy single-stepping it. It's throwing me a user break point in _heap_alloc_dbg at if (lRequest == _crtBreakAlloc) _CrtDbgBreak(); I guess this is an "out of heap memory" error. The App basically builds lots of models, tests them and then kicks the results out to a text file, so I'm creating and destroying a lot of objects, which is why I think I've a memory leak. The break actually comes during a call to CArray::SetSize, although I do apprec...