Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement
I am trying to create an IF that looks for a date range from one column and
then counts items in another column. The formula I have below is not looking at the MONTH field. Any suggestions? Thanks! =IF(MONTH(SHEET1!A2:A500*(2)),COUNTIF(SHEET1!G2:G5 00,"Successful")) MONTH is looking at SHEET1 in cells A2 to A500 for February (2 month) The next statement should look at only February data and then count how many times the word "Successful" is found. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement
Hi Jeff,
If I understand you right, you should be able to use SUMPRODUCT. So let's say column A contains a series of dates and column B contains a list of corresponding values, of which you're interested in matching on the word "successful." If that's the case, try this: =SUMPRODUCT(--(MONTH(Sheet1!A2:A500)=2),--(Sheet1!G2:G500="Successful")) This will look for all instances where the month = 2 (February) and return a count of the corresponding columns that contain the string "Successful." Jim Jeff H. wrote: I am trying to create an IF that looks for a date range from one column and then counts items in another column. The formula I have below is not looking at the MONTH field. Any suggestions? Thanks! =IF(MONTH(SHEET1!A2:A500*(2)),COUNTIF(SHEET1!G2:G5 00,"Successful")) MONTH is looking at SHEET1 in cells A2 to A500 for February (2 month) The next statement should look at only February data and then count how many times the word "Successful" is found. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement
One option would be to create a helper column which concatenates (joins) the
two columns, and run your COUNTIF on that. Example: =CONCATENATE(MONTH(A2),G2) Fill that down as necessary, and then, assuming the concatenation is done in column J, you could use the COUNTIF function: =COUNTIF(J2:J500="2Successful") -- Brevity is the soul of wit. "Jeff H." wrote: I am trying to create an IF that looks for a date range from one column and then counts items in another column. The formula I have below is not looking at the MONTH field. Any suggestions? Thanks! =IF(MONTH(SHEET1!A2:A500*(2)),COUNTIF(SHEET1!G2:G5 00,"Successful")) MONTH is looking at SHEET1 in cells A2 to A500 for February (2 month) The next statement should look at only February data and then count how many times the word "Successful" is found. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement
Thank you both for your help. I really appreciate your assistance.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL concatenation statement | Excel Discussion (Misc queries) | |||
Can't add 7th IF statement to long formula. | Excel Worksheet Functions | |||
SET statement tutorial | Excel Discussion (Misc queries) | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions |