Formula Help

[nelmagene]

Hello, I need help with a formula. I am trying to calculate a value in a specific date range. In Column I, I have dates and I need to track the status of the reports which are listed in column x. I have tried just using cell ranges but the reports are in numerical order not date order, so i end up missing a couple that are out of date sequence.

this is the formula I am trying to use but it returns 0
=countifs(NCR!I105:I510,">=9/1/2011",NCR!i105:I510,"=R")

as I said this returns 0 however in this case it should be 1

I have tried expressing it in many different ways but this is the closest I can get.

there are 3 different statuses open=blank, closed=c, repealed=R

I need to calculate how many reports are in each category per month.

I hope this is clear enough. I have been trying to figure this out on my own all day with no luck.

Thank you in advance.

 

[Jose_Chasez]

Should your second condition ("=R" in your example) be looking at column X instead of column I?

 

[nelmagene]

Yes it should and it is mistyped it I will correct.

 

[nelmagene]

=countifs(NCR!I105:I510,">=9/1/2011",NCR!X105:X510,"=R")

Corrected formula

 

[T. Valko]

=countifs(NCR!I105:I510,">=9/1/2011",NCR!X105:X510,"=R")

Corrected formula

You're formula should work as written but it would be better to use cells to hold the criteria:

A1 = 9/1/2011
B1 = R

=COUNTIFS(NCR!I105:I510,">="&A1,NCR!X105:X510,B1)

If you're getting an incorrect result then you probably have data problems.

Dates may not be true Excel dates. They may be TEXT strings that look like dates.

You might have leading/trailing whitespace characters in cells...

See this for common data problems:

http://contextures.com/xlFunctions02.html#Trouble

 

[nelmagene]

Thanks for the information. I have relooked at all the cell formatting, and it is correct.

When I just use =count and either half of this formula they both work as they are suppose to. So each agruement is valid by itself but they are not playing nice together.


Thanks again for your input