Counting items in 2 different columns 2

[shawnmandel]

Hi,

I have two columns:

Column C is Status
Column D is Priority

Possible values for status is Open, Closed or Pendging
Possible values for Priority is Low, Medium, or High

I need to create a row total that counts all items with BOTH an Open status AND High priority.

How do I create a formula that counts values from different rows? I'm suspecting I may need to use a combination of COUNTIFs and VLOOKUPs? What's the easiest method?


Thanks,
Shawn

 

[Zathras]

Assuming your data are in a standard Excel list format (see Help file for "Guidelines for creating a list on a worksheet"), the easiest way would be to use the =DCOUNT() funtion with a 2-column Critera range.

 

[mscallisto]

in col e make a formula =c1&d1 and copy it down to last row

in col f make a formula =COUNTIF(E:E,INDEX(E1,1,1))and copy it down to last row to get your counts


The are better ways but this works

 

[shawnmandel]

mscallisto, thanks, I'll give it a try.

Zathras, how would I use the DCOUNT() formula for my specific case. I'm getting results without success. I do have a standard Excel list format.

Regards,
Shawn

 

[shawnmandel]

mscallisto, it's not giving me what I'm looking for. I need a count of ONLY status "Open" and priority "High".

It appears to be counting the rows regardless of this specific combination.

Thanks,
Shawn

 

[mscallisto]

It counts all the combo's

You are better off using Zathras'
DCOUNT() "Counts cells that contain numbers"

BTW
DCOUNTA() "Counts nonblank cells"

Help DCOUNT() shows good examples to follow

 

[Zathras]

Assume the following data in A1:E17
[tt]
Number Class Status Priority Volume
1 100 Open Low 1001
2 105 Open Medium 1002
3 110 Open High 1003
4 115 Open High 1004
5 120 Closed Low 1005
6 125 Closed Medium 1006
7 130 Closed High 1007
8 135 Closed High 1008
9 140 Closed Low 1009
10 145 Pending Medium 1010
11 150 Pending High 1011
12 155 Pending High 1012
13 160 Pending Low 1013
14 165 Pending Medium 1014
15 170 Pending High 1015
16 175 Open High 1016
[/tt]
Give it a range name of "Database"

Then put this in G1:H2
[tt]
Status Priority
Open High
[/tt]
Give that a range name of "Criteria"

Then this formula will give you the desired count:
[tt]
=DCOUNT(Database,1,Criteria)
[/tt]
If you prefer, you don't have to assign range names. You can use
[tt]
=DCOUNT($A$1:$E$17,1,$G$1:$H$2)
[/tt]
but the use of range names is encouraged. You don't have to use "Database" and "Criteria" either, you can use any name you choose.

 

[shawnmandel]

Zathras, that's perfect!

Thank-you very much,
Shawn

 

[mscallisto]

To add to Zathra's post":

The field argument is optional. If field is omitted, DCOUNT counts all records in the database that match the criteria.



Assume the following data in A1:B5

Status Priority
Open Low
Open Medium
Open High
Open High

Give it a range name of "Database"

Then put this in C12

Status Priority
Open High

Give that a range name of "Criteria"

Then this formula will give you the desired count:

=DCOUNT(Database,,Criteria)