| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I correct error using the =IF(AND(formula in excel?
Hello, can you help?
I would like to correct the following formula...This is what I have... =IF(B1:B4,"=01/01/06"),THENCOUNT,(C1:C4,IF=A1)) This is what my worksheet shows..... Column A Column B Column C Column D A 12/20/05 A (Above Formula)..This should=2 B 12/21/05 C C 01/03/06 A D 01/21/06 A Can you please help me in how to write the formula correctly??? Thanks so much! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I correct error using the =IF(AND(formula in excel?
=SUMPRODUCT(--(B1:B4=--"2006-01-01"),--(C1:C4="A"))
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Redwing ML" <Redwing wrote in message ... Hello, can you help? I would like to correct the following formula...This is what I have... =IF(B1:B4,"=01/01/06"),THENCOUNT,(C1:C4,IF=A1)) This is what my worksheet shows..... Column A Column B Column C Column D A 12/20/05 A (Above Formula)..This should=2 B 12/21/05 C C 01/03/06 A D 01/21/06 A Can you please help me in how to write the formula correctly??? Thanks so much! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I correct error using the =IF(AND(formula in excel?
=SUMPRODUCT(--(B1:B4=DATE(2006,1,1))*--(C1:C4=A1))
"Redwing ML" wrote: Hello, can you help? I would like to correct the following formula...This is what I have... =IF(B1:B4,"=01/01/06"),THENCOUNT,(C1:C4,IF=A1)) This is what my worksheet shows..... Column A Column B Column C Column D A 12/20/05 A (Above Formula)..This should=2 B 12/21/05 C C 01/03/06 A D 01/21/06 A Can you please help me in how to write the formula correctly??? Thanks so much! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I correct error using the =IF(AND(formula in excel?
Are you trying to count how many values in C are = or 01/01/2006?
If so, =COUNTIF(B1:B4,"=01/01/2006") If you want to count with more than one criteria (say B = a date and A = C) =SUMPRODUCT(--(B1:B10=DATE(2006,1,1)), --(A1:A10=C1:C10)) (I used 10 rows to test the data; I use DATE since I work with the other date system - dd/mm/yyyy) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Redwing ML" <Redwing wrote in message ... Hello, can you help? I would like to correct the following formula...This is what I have... =IF(B1:B4,"=01/01/06"),THENCOUNT,(C1:C4,IF=A1)) This is what my worksheet shows..... Column A Column B Column C Column D A 12/20/05 A (Above Formula)..This should=2 B 12/21/05 C C 01/03/06 A D 01/21/06 A Can you please help me in how to write the formula correctly??? Thanks so much! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I correct error using the =IF(AND(formula in excel?
No need for double negatives when you use * operator, only when the two
arrays are set apart by comma as in =SUMPRODUCT(--(B1:B4=DATE(2006,1,1)),--(C1:C4=A1)) See http://mcgimpsey.com/excel/formulae/doubleneg.html wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Duke Carey" wrote in message ... =SUMPRODUCT(--(B1:B4=DATE(2006,1,1))*--(C1:C4=A1)) ! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I correct error using the =IF(AND(formula in excel?
Thanks Bernard
"Bernard Liengme" wrote: No need for double negatives when you use * operator, only when the two arrays are set apart by comma as in =SUMPRODUCT(--(B1:B4=DATE(2006,1,1)),--(C1:C4=A1)) See http://mcgimpsey.com/excel/formulae/doubleneg.html wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Duke Carey" wrote in message ... =SUMPRODUCT(--(B1:B4=DATE(2006,1,1))*--(C1:C4=A1)) ! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I correct error using the =IF(AND(formula in excel?
Thank you again for the help but, it must be me? Iam still trying to get your
suggestion to work completely. Needed=IF(B1:B4,"=01/01/06"),THENCOUNT,(C1:C4,IF=A1)) Your way =SUMPRODUCT(--(B1:B10=DATE(2006,1,1)), --(A1:A10=C1:C10))does count but, if i change the actual date in a cell from B to be less than the date of 2006,1,1 the quantity does not adjust? Can you help again?? Thanks "Bernard Liengme" wrote: Are you trying to count how many values in C are = or 01/01/2006? If so, =COUNTIF(B1:B4,"=01/01/2006") If you want to count with more than one criteria (say B = a date and A = C) =SUMPRODUCT(--(B1:B10=DATE(2006,1,1)), --(A1:A10=C1:C10)) (I used 10 rows to test the data; I use DATE since I work with the other date system - dd/mm/yyyy) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Redwing ML" <Redwing wrote in message ... Hello, can you help? I would like to correct the following formula...This is what I have... =IF(B1:B4,"=01/01/06"),THENCOUNT,(C1:C4,IF=A1)) This is what my worksheet shows..... Column A Column B Column C Column D A 12/20/05 A (Above Formula)..should=2 B 12/21/05 C C 01/03/06 A D 01/21/06 A Can you please help me in how to write the formula correctly??? Thanks so much! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
| TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
| Value Errors with EXCEL XP not showing up in EXCEL 2000 | Links and Linking in Excel | |||
| Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
| Is it possible to export the (Excel) auto correct "dictionary" to. | Excel Discussion (Misc queries) | |||
Linear Mode
