Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with dreaded formulas...
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas not recognizing new data | Excel Discussion (Misc queries) | |||
Countif formulas change after doing a sort | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |