How do I do a multiple criteria Count?

October 6, 2010 at 07:13:39
Specs: Windows XP
Hi

HELP me please. Below is a snap shot of a database i am looking at.

What I am trying and failing to do is count the number of "IN PROCESS WTW" cells that are less than 7 days old.

The the seven days are calculated from the date this data is entered (data is entered and altered to COMPLETE on a daily basis) subtracted from todays date. Then once a week I have to take the summary (which is what I am attempting to complete/set up) and report to management.

In effect I am trying to cacluce the number of jobs added to the database over the past seven days, where each row is one job. (there are about 35columns).

I will in the end need to calaculte the IN PROCESS WTW, IN PROCESS STC and IN PROCESS PS.. but think I can manage two if someone can help with at least one? PLEASE...

Can ANYONE HELP ME please... have been attempting count ifs and sumifs and various other options All day!

Thanks


WTW, STC or PS Contractor /SW Days Old Complete Concat
WTW Assets 3 In process WTW
STC BTU 5 In process STC
WTW BTU 9 In process WTW
PS BTU 155 In process PS
PS BTU 155 In process PS
PS BTU 155 In process PS
PS SW 155 In process PS
STC BTU 121 In process STC
WTW BTU 7 In process WTW
WTW BTU 2 In process WTW
WTW SW 309 CompleteWTW
WTW BTU 163 In process WTW


See More: How do I do a multiple criteria Count?

Report •


#1
October 6, 2010 at 07:14:36
HI

The table has not come out very well...
WTW, STC or PS Contractor /SW Days Old Complete Concat
WTW Assets 3 In process WTW
STC BTU 5 In process STC
WTW BTU 9 In process WTW
PS BTU 155 In process PS
PS BTU 155 In process PS
PS BTU 155 In process PS
PS SW 155 In process PS
STC BTU 121 In process STC
WTW BTU 7 In process WTW
WTW BTU 2 In process WTW
WTW SW 309 CompleteWTW
WTW BTU 163 In process WTW



Report •

#2
October 6, 2010 at 07:38:58
The the seven days are calculated from the date this data is entered (data is entered and altered to COMPLETE on a daily basis) subtracted from todays date.

So where is the Date the data was entered?
Without an Entry Date, how can you figure the 7 days?

MIKE

http://www.skeptic.com/


Report •

#3
October 6, 2010 at 07:43:16
When posting your data, use the PRE tags located above the message box, helps line everything up, then use the Preview Follow Up button on the bottom to see how it all looks.
If needed there is a
Check To Show Confirmation Page Again
box above, click the box and you can see your new modifications. Just keep clicking the box, review your message, when your happy click the Confirm and See Post without clicking the box.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 6, 2010 at 07:44:49
Dates entered are various. The Days old data in my table above (3,5.9,155) is the calculation. So from this column which already tells me how old the entry is, I need to pull out those less than 7?

I have 9 tabs and the 10th tab is the summary. the 10th Tab contains one cell for summary for each tab... tis a pain to count manually so I want the cell to self populate as new data is input.


Report •

#5
October 6, 2010 at 07:49:15
WTW, STC or PS Contractor Days Old Complete Concat
WTW Assets 3 In process WTW
STC BTU 5 In process STC
WTW BTU 9 In process WTW
PS BTU 155 In process PS
PS BTU 155 In process PS
PS BTU 155 In process PS
PS SW 55 In process PS
STC BTU 121 In process STC
WTW BTU 7 In process WTW
WTW BTU 2 In process WTW
WTW SW 309 CompleteWTW
WTW BTU 163 In process WTW

Report •

#6
October 6, 2010 at 08:10:04
If your data looks like this:
     A     B       C          D
1)  WTW   Assets    3   In process WTW
2)  STC   BTU       5   In process STC
3)  WTW   BTU       9   In process WTW
4)   PS   BTU     155   In process PS
5)   PS   BTU     155   In process PS
6)   PS   BTU     155   In process PS
7)   PS    SW     155   In process PS
8)  STC   BTU     121   In process STC
9)  WTW   BTU       7   In process WTW
10) WTW   BTU       2   In process WTW
11) WTW   SW      309   Complete WTW
12) WTW   BTU     163   In process WTW

Then this ARRAY formula should work:

=SUM((C1:C12<7)*(D1:D12="In process WTW"))

With an ARRAY formula, after you enter the formula, DO NOT PRESS ENTER....
You MUST use CTRL-SHIFT-ENTER
You should see curly braces surrounding the formula like:

{=SUM((C1:C12<7)*(D1:D12="In process WTW"))}

MIKE

http://www.skeptic.com/


Report •

#7
October 6, 2010 at 08:20:31
Hi

THanks - but its not working... am gettnig a #Num! error when i hit ctl+shft+enter and I get 0 if i just hit enter

would underlying formulas affect this? Like the data for IN Process WTW is made up a concatenate of two columns, one colume contains In Process or Complete adn the second column contains WTW, STC or PS?

:(


Report •

#8
October 6, 2010 at 08:52:46
would underlying formulas affect this?
No they should not and I'm unable to duplicate your problem.

Post your exact formula.

A Num error can occur when you use an unacceptable argument in a function that requires a numeric argument.

MIKE

http://www.skeptic.com/


Report •

#9
October 6, 2010 at 09:30:57
Here is another ARRAY formula that uses =COUNT and =IF:

=COUNT(IF(C1:C12<7,IF(D1:D12="In Process WTW",)))

Again, it's an ARRAY formula, use CTRL-SHIFT-ENTER

MIKE

http://www.skeptic.com/


Report •

#10
October 6, 2010 at 10:11:59
THanks.... think I got it sussed!

:)

Now all I need to do is repeat the formula 8 more times and then start the other data set of 9 tabs... fun times.

Cheers


Report •

#11
October 6, 2010 at 10:50:48
Glad I could help.

MIKE

http://www.skeptic.com/


Report •

Ask Question