DCOUNT for non-contiguous columns

[RiotLoadTime]

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 concatenate two databases to somehow combine A:D and K just for the function?

Thanks,
RiotLoadTime

[Domenic]

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 <[email protected]>,
RiotLoadTime
<[email protected]> 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

[Peo Sjoblom]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[RiotLoadTime]

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.

[Dav]

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

Regards

Dav