Countif issues

chriscarr

New Member
Joined
Aug 22, 2010
Messages
29
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
Code:
=IF(AND(A3="",B3>0),1,"")

then count those 1's
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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...
Code:
=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...
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top