HELP with dreaded formulas...
 

OK...I am trying to enter a formula that is based on several
criteria....criteria that is derived from different columns of a different
worksheet. I assume this is possible, let me try and explain...

On one worksheet, I would like to have a formula that counts cells of
another worksheet that have data in them, but only if a few criteria are
satisfied in each row of the 2nd worksheet. For example, I want to generate a
number of entries in column C, only if column B and/or A meet certain
criteria for each specific row in that same worksheet. So, in cell C1, I
would like to know if there is some data present, but only if cell B1 meets
certain criteria and A1 meets certain criteria (say the date in A1 must be
todays date, otherwise I don't care...or the name in B1 must be a certain
name). And, suppose we are talking about the first 5 rows...so I am
ultimately looking for a SUM. How would I show that column C SUM in a
specific cell on another worksheet?

I hope I explained that well enough. Thanks in advance for any help!!!

HELP with dreaded formulas...
 

Look into array formulas at Chip Pearson's web site:

http://www.cpearson.com/excel/array.htm

HELP with dreaded formulas...
 

Thanks, I will do that...I just hope its on my level.

"Kletcho" wrote:

Look into array formulas at Chip Pearson's web site:

http://www.cpearson.com/excel/array.htm

HELP with dreaded formulas...
 

After playing around with the suggestion you made Kletcho...I have run into
one problem...I need to add up cells with data...I suppose this means I have
to use the COUNTA or COUNTIF function...still lost...HELP

Thanks Again...



"Kletcho" wrote:

Look into array formulas at Chip Pearson's web site:

http://www.cpearson.com/excel/array.htm

HELP with dreaded formulas...
 

If you want a formula in sheet1 to sum column C in sheet 2 whenever the
same row contains a specific date in column A and a specific name in
column B then try.

=SUMPRODUCT(--(sheet2!A1:A100=F2),--(sheet2!B1:B100=G2),sheet2!C1:C100)

where F2 (in sheet 1) contains your date and G2 your name.

extend ranges if necessary


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=528798

HELP with dreaded formulas...
 

To count if certain criteria are met and cells are not blank you can
tweak daddylonglegs formula a bit:

=SUMPRODUCT(--(sheet2!A1:A100=F2),--(sheet2!B1:B100=G2),--(sheet2!C1:C100<""),1)


Or as done in an array formula:

=sum((sheet2!A1:A100=F2)*(sheet2!B1:B100=G2)*(shee t2!C1:C100<"")*1)
(remember to enter the formula using ctrl + shift + enter)

Sumproducts are usually a little faster in calculating than array
formulas.