HELP with dreaded formulas...

[Brian]

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!!!

 

[Kletcho]

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

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

 

[Brian]

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

 

[Brian]

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

 

[daddylonglegs]

If you want a formula in sheet1 to sum column C in sheet 2 whenever th
same row contains a specific date in column A and a specific name i
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 necessar

 

[Kletcho]

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)*(sheet2!C1:C100<>"")*1)
(remember to enter the formula using ctrl + shift + enter)

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