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
447 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: