Countif issues

[chriscarr]

Hi guys

I'm working on an order log which is relyig on some countifs to keep track of it all. The countif's I'm trying to get sorted are:

Countif a cell's date is more than 7 days historic of today's date but anything 7 days or less isn't counted.

Countif of the number of orders in different statuses based on today's date. These need to stay at the last figure when the date changes so I can track the historic movements of the orders.

The final one is to count a blank cell if a cell in another column is not blank.

I know this is a lot to ask but I was wondering if it was possible in Excel 2003.

Many thanks

Chris

 

[mole999]

is it possible to add helper columns, so that The final one is to count a blank cell if a cell in another column is not blank. is evaluated in the helper, and then count the number of positive values

i.e

=IF(AND(A3="",B3>0),1,"")

then count those 1's

 

[Aladin Akyurek]

Hi guys

I'm working on an order log which is relyig on some countifs to keep track of it all. The countif's I'm trying to get sorted are:

Countif a cell's date is more than 7 days historic of today's date but anything 7 days or less isn't counted.

Countif of the number of orders in different statuses based on today's date. These need to stay at the last figure when the date changes so I can track the historic movements of the orders.

The final one is to count a blank cell if a cell in another column is not blank.

I know this is a lot to ask but I was wondering if it was possible in Excel 2003.

Many thanks

Chris

Not very clear... Guessing that:

1)

=COUNTIF(DateRange,TODAY()-7)

2)

=SUMPRODUCT(--(DateRange=TODAY()),--(StatusRange=Status))

3)

=SUMPRODUCT(--(RangeWithBlanks=""),--(OtherRange<>""))

Advise. Be specific: Provide a small sample along with the desired result(s).

Hope this helps...

 

[chriscarr]

Hi Aladin

Thanks for those, I've tried to attach the report I'm working on but my PC wouldn;t allow it.

1. Could you change this on to include point 3, so that if D is not blank but L is and the date in column K is more that 7 days prior to today it counts it?

2. If the summary of a status is C4 and the record for today is F5, could you adjust the formula accordingly for me? I can ammend it once it's in sheet. Will this keep the previous days data in there?

3. My summary sheet needs to count the blank cells in column L if there is an entry in column D for today's date.

Thanks for your help.

Chris

 

[Aladin Akyurek]

Hi Aladin

Thanks for those, I've tried to attach the report I'm working on but my PC wouldn;t allow it.

1. Could you change this on to include point 3, so that if D is not blank but L is and the date in column K is more that 7 days prior to today it counts it?

Is it...

=SUMPRODUCT(
    --($D$2:$D$100 <> ""),
    --($L$2:$L$100 = ""),
    --($K$2:$K$100 < TODAY()-7))

2. If the summary of a status is C4 and the record for today is F5, could you adjust the formula accordingly for me? I can ammend it once it's in sheet. Will this keep the previous days data in there?

The date range is in K (see previous specs of yours). Where is the status range?

3. My summary sheet needs to count the blank cells in column L if there is an entry in column D for today's date.
...

Looks like...

=SUMPRODUCT(--($L$2:$L$100=""),--($D$2:$D$100<>""),--($K$2:$K$100=F5))

where F5 houses

=TODAY()

Is this even close?

If not, you need to specify the ranges pricisely and the conditions they must satisfy...