DCOUNT for non-contiguous columns
 

I have a database with multiple-cell headers (descriptors like "1 hour"
and "2 hour") in the first row, under which I have cell-specific
headers (like "blood pressure," "temperature") in the second row, and
data in the rest of the rows. I need to count all the numbers in
A3:A200 where the corresponding B, C, D, and K values (i.e. the B,C,D,
and K values in the same row) are equal to zero.

It'd be nice if I could define my database for the function(i.e.
"DCOUNT(database,field, criteria)") to be A2:K200, have the field=1,
and make a criteria array with the first row being the headers for
B,C,D, and K, and the second row being 0. Unfortunately, I can't do
that because G2 actually has the same heading as K2 (they're both blood
pressure, they're just at two different times, as shown by them being
under A1 and B1 respectively).

Is there any way for me to make the database have just columns A, B, C,
D, and K without rearranging them or modifying their headers? Is there
anyway to truncate two databases to somehow combine A:D and K just for
the function?

Thanks,
RiotLoadTime


--
RiotLoadTime
------------------------------------------------------------------------
RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956
View this thread: http://www.excelforum.com/showthread...hreadid=557470

DCOUNT for non-contiguous columns
 

Can you provide a sample of your data (about 5 rows of Columns A, B, C,
D and K), along with the expected results?

In article ,
RiotLoadTime
wrote:

I have a database with multiple-cell headers (descriptors like "1 hour"
and "2 hour") in the first row, under which I have cell-specific
headers (like "blood pressure," "temperature") in the second row, and
data in the rest of the rows. I need to count all the numbers in
A3:A200 where the corresponding B, C, D, and K values (i.e. the B,C,D,
and K values in the same row) are equal to zero.

It'd be nice if I could define my database for the function(i.e.
"DCOUNT(database,field, criteria)") to be A2:K200, have the field=1,
and make a criteria array with the first row being the headers for
B,C,D, and K, and the second row being 0. Unfortunately, I can't do
that because G2 actually has the same heading as K2 (they're both blood
pressure, they're just at two different times, as shown by them being
under A1 and B1 respectively).

Is there any way for me to make the database have just columns A, B, C,
D, and K without rearranging them or modifying their headers? Is there
anyway to truncate two databases to somehow combine A:D and K just for
the function?

Thanks,
RiotLoadTime

DCOUNT for non-contiguous columns
 

One way

=DCOUNT(Database,"1 hour",M1:P2)

assuming top header for A is called "1 hour" then use the other headers (B:D
and K) in (in this example) M1:P1 and in M2:P2 put 0
It doesn't matter if you have an extra header in the second row, use the
first row

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"RiotLoadTime"
wrote in message
news:RiotLoadTime.2a8evc_1151698803.8238@excelforu m-nospam.com...

I have a database with multiple-cell headers (descriptors like "1 hour"
and "2 hour") in the first row, under which I have cell-specific
headers (like "blood pressure," "temperature") in the second row, and
data in the rest of the rows. I need to count all the numbers in
A3:A200 where the corresponding B, C, D, and K values (i.e. the B,C,D,
and K values in the same row) are equal to zero.

It'd be nice if I could define my database for the function(i.e.
"DCOUNT(database,field, criteria)") to be A2:K200, have the field=1,
and make a criteria array with the first row being the headers for
B,C,D, and K, and the second row being 0. Unfortunately, I can't do
that because G2 actually has the same heading as K2 (they're both blood
pressure, they're just at two different times, as shown by them being
under A1 and B1 respectively).

Is there any way for me to make the database have just columns A, B, C,
D, and K without rearranging them or modifying their headers? Is there
anyway to truncate two databases to somehow combine A:D and K just for
the function?

Thanks,
RiotLoadTime


--
RiotLoadTime
------------------------------------------------------------------------
RiotLoadTime's Profile:
http://www.excelforum.com/member.php...o&userid=35956
View this thread: http://www.excelforum.com/showthread...hreadid=557470

DCOUNT for non-contiguous columns
 

A B C D E F


1 | 1 Hour | 2 Hour
|

2 | Bld. Prssre.| Temp. | Weight| Bld. Prssre.|Temp.|Weight|

3 | 1 0 1 1 0
0

4 | 0 0 0 1 1
1

5 | 0 1 1 0 1
0

6 | 1 0 0 0 0
0


Someone asked for a sample, so here is the best I could do. Using the
sample above, I'd want to count all the cells in column A where A=0,
B=0, and F=0. Using DCOUNT, you can't make your database A2:F6
because you'd include two "Temp." columns, so I wouldn't be able to
distinguish the two in my criteria database (i.e. I want the temp. at 1
hour to be 0, but I don't care what the temp at 2 hours is). The
columns can't be re-arranged or changed (for reasons out of my
control).

Thanks guys,
RiotLoadTime

P.S. I couldn't figure out how to format the response (apparently it
ignored the spaces I put between the numbers in the sample), so that's
why the sample came out all smushed together.


--
RiotLoadTime
------------------------------------------------------------------------
RiotLoadTime's Profile: http://www.excelforum.com/member.php...o&userid=35956
View this thread: http://www.excelforum.com/showthread...hreadid=557470

DCOUNT for non-contiguous columns
 

=sumproduct((a2:a6=0)*(b2:b6=0)*(f2:f6=0))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=557470