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




--
JTKrupa
------------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php...o&userid=27759
View this thread: http://www.excelforum.com/showthread...hreadid=472703

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


--
shternm
------------------------------------------------------------------------
shternm's Profile: http://www.excelforum.com/member.php...nfo&userid=858
View this thread: http://www.excelforum.com/showthread...hreadid=472703

how do I combine these two functions?


--
JTKrupa
------------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php...o&userid=27759
View this thread: http://www.excelforum.com/showthread...hreadid=472703

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...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=472703

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?

I appreciate all your help, but after further investigation on my part
as to just what my supervisor is asking to be done, I'll have to do it
in Access, which I can do just fine. The current Access generated
results do not give enough information to do what I had in mind with
Excel. The query only returns ~900 rows, and the summary of the report
I'm trying to automate has to account for 40,000 lines.

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

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

Thanks everyone!!

-JT




--
JTKrupa
------------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php...o&userid=27759
View this thread: http://www.excelforum.com/showthread...hreadid=472703

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 these
criteria.

OTHERWISE, display "0"

Any suggestions?

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

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

Thanks!!

-JT


--
JTKrupa
------------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php...o&userid=27759
View this thread: http://www.excelforum.com/showthread...hreadid=472703

I don't want to "beat a dead horse" but to make sure everyone is on the
same page...

For each row on Sheet B (2:897) I need to have it look at the values in
columns B and E, and compare them with the values on Sheet A (Cell A9
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 in
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 in
person...I'm trying to give as much info as I can...I apologize if
anyone is offended by my "lowly" description (i.e. trying to explain it
to a non-Excel user) but I have found that sometimes breaking it down to
the absolute basics makes sure we all understand on the master level of
what is trying to be accomplished.

Thanks again!

-JT :)


--
JTKrupa
------------------------------------------------------------------------
JTKrupa's Profile: http://www.excelforum.com/member.php...o&userid=27759
View this thread: http://www.excelforum.com/showthread...hreadid=472703

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(she etB!$B$2:$B$897,sheetA!$A$9),0)

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=472703