Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formulas
I have a formula that is to reslut in counting the number of incidences if
the line meets the criteria. 1. Jan (Month) 2. Sun (Day of the Month) 3. 11 (Hour of the Day) 4. The fourth criteria column just has a 1 in it to count. If the line meets all 3 criteria count it otherwise put a zero in the cell. Sum(IF($B$5:$B$5034="Jan",IF($H$5:$H$5034-"Sun", IF($J$5:$J$5034=11,$I$5:$I$5034,0),0))) This formula works for me with the "Jan" and the 11, but when I add the third criteria "Sun" it gives me a zero. Hope someone can help. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formulas
In just a quick glance, if you copied your formula and pasted, you have
-"Sun" instead of ="Sun" -- ** John C ** "numbersgirl" wrote: I have a formula that is to reslut in counting the number of incidences if the line meets the criteria. 1. Jan (Month) 2. Sun (Day of the Month) 3. 11 (Hour of the Day) 4. The fourth criteria column just has a 1 in it to count. If the line meets all 3 criteria count it otherwise put a zero in the cell. Sum(IF($B$5:$B$5034="Jan",IF($H$5:$H$5034-"Sun", IF($J$5:$J$5034=11,$I$5:$I$5034,0),0))) This formula works for me with the "Jan" and the 11, but when I add the third criteria "Sun" it gives me a zero. Hope someone can help. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formulas
You are correct. Unfortunately, this is just a typo - the actual formula
uses ="Sun". Any other observations? Thanks for the response. "John C" wrote: In just a quick glance, if you copied your formula and pasted, you have -"Sun" instead of ="Sun" -- ** John C ** "numbersgirl" wrote: I have a formula that is to reslut in counting the number of incidences if the line meets the criteria. 1. Jan (Month) 2. Sun (Day of the Month) 3. 11 (Hour of the Day) 4. The fourth criteria column just has a 1 in it to count. If the line meets all 3 criteria count it otherwise put a zero in the cell. Sum(IF($B$5:$B$5034="Jan",IF($H$5:$H$5034-"Sun", IF($J$5:$J$5034=11,$I$5:$I$5034,0),0))) This formula works for me with the "Jan" and the 11, but when I add the third criteria "Sun" it gives me a zero. Hope someone can help. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formulas
Try this normally entered formula:
=SUMPRODUCT(--($B$5:$B$5034="Jan"),--($H$5:$H$5034="Sun"),--($J$5:$J$5034=11)) Better to use cells to hold the criteria: A1 = Jan B1 = Sun C1 = 11 =SUMPRODUCT(--($B$5:$B$5034=A1),--($H$5:$H$5034=B1),--($J$5:$J$5034=C1)) -- Biff Microsoft Excel MVP "numbersgirl" wrote in message ... I have a formula that is to reslut in counting the number of incidences if the line meets the criteria. 1. Jan (Month) 2. Sun (Day of the Month) 3. 11 (Hour of the Day) 4. The fourth criteria column just has a 1 in it to count. If the line meets all 3 criteria count it otherwise put a zero in the cell. Sum(IF($B$5:$B$5034="Jan",IF($H$5:$H$5034-"Sun", IF($J$5:$J$5034=11,$I$5:$I$5034,0),0))) This formula works for me with the "Jan" and the 11, but when I add the third criteria "Sun" it gives me a zero. Hope someone can help. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formulas
What is in columns B, H and J... real Excel dates formatted to look like the
month name, day name and hour or do you actually have text, or formulas returning text, that show the entries you listed? -- Rick (MVP - Excel) "numbersgirl" wrote in message ... I have a formula that is to reslut in counting the number of incidences if the line meets the criteria. 1. Jan (Month) 2. Sun (Day of the Month) 3. 11 (Hour of the Day) 4. The fourth criteria column just has a 1 in it to count. If the line meets all 3 criteria count it otherwise put a zero in the cell. Sum(IF($B$5:$B$5034="Jan",IF($H$5:$H$5034-"Sun", IF($J$5:$J$5034=11,$I$5:$I$5034,0),0))) This formula works for me with the "Jan" and the 11, but when I add the third criteria "Sun" it gives me a zero. Hope someone can help. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formulas
See Biff's response for a good formula (T. Valko). If that answer doesn't
work for you, Rick posed some questions that would need to be answered. -- ** John C ** "numbersgirl" wrote: You are correct. Unfortunately, this is just a typo - the actual formula uses ="Sun". Any other observations? Thanks for the response. "John C" wrote: In just a quick glance, if you copied your formula and pasted, you have -"Sun" instead of ="Sun" -- ** John C ** "numbersgirl" wrote: I have a formula that is to reslut in counting the number of incidences if the line meets the criteria. 1. Jan (Month) 2. Sun (Day of the Month) 3. 11 (Hour of the Day) 4. The fourth criteria column just has a 1 in it to count. If the line meets all 3 criteria count it otherwise put a zero in the cell. Sum(IF($B$5:$B$5034="Jan",IF($H$5:$H$5034-"Sun", IF($J$5:$J$5034=11,$I$5:$I$5034,0),0))) This formula works for me with the "Jan" and the 11, but when I add the third criteria "Sun" it gives me a zero. Hope someone can help. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |