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

[JTKrupa]

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

 

[shternm]

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

 

[JTKrupa]

how do I combine these two functions?

 

[Morrigan]

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?

 

[Roger Govier]

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]

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

 

[JTKrupa]

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

 

[JTKrupa]

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 :

 

[pinmaster]

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