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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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...
 

Forum statistics

Threads
1,141,139
Messages
5,704,508
Members
421,353
Latest member
jekoxien15

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
Top