GOING CRAZY! - Count Functions

**keith** · Mar 24th, 2002, 03:07 PM

must have been syntax.. Or I think I may have overlaped columns or something by mistake.. works now.. thanks..

**Yogi Anand** · Mar 24th, 2002, 03:20 PM

On 2002-03-24 11:05, keith wrote:
well it seems to work (I hope its not just working for some wierd coincidence).. my end goal is to count everytime a "2" shows up in ANY ITEM COLUMN where a specific date is met in the DATA COLUMN. Keep in mind I am not counting rows but cells that contain "2". So if the date matches and ITEM 1=2 and ITEM2=2 then the formula result should be "2". If item1=1 and item2=2 the result should be "1" and vice versa.. Am I all messed up?

Hey Keith and Aladin:
Looks like we are really beating at it. Keith I am not trying to convince you to use the DCOUNT function but it does work.Here I have used the data used in Aladin's example with the criteria being that Date is 37338 and Item1=2 tocount the number of items meeting the criteria in item1 column, and then use the DCOUNT function again with the criteria that Date is 37338 and item2=2 ...
so here is what we will get

=DCOUNT(A1:C4,"item1",F1:G2)+DCOUNT(A1:C4,"Item2",H1:I2)
resulting in 3

T H A N K S ! for keeping this discussion alive.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

**Aladin Akyurek** · Mar 24th, 2002, 03:31 PM

On 2002-03-24 15:20, Yogi Anand wrote:

On 2002-03-24 11:05, keith wrote:
well it seems to work (I hope its not just working for some wierd coincidence).. my end goal is to count everytime a "2" shows up in ANY ITEM COLUMN where a specific date is met in the DATA COLUMN. Keep in mind I am not counting rows but cells that contain "2". So if the date matches and ITEM 1=2 and ITEM2=2 then the formula result should be "2". If item1=1 and item2=2 the result should be "1" and vice versa.. Am I all messed up?

Hey Keith and Aladin:
Looks like we are really beating at it. Keith I am not trying to convince you to use the DCOUNT function but it does work.Here I have used the data used in Aladin's example with the criteria being that Date is 37338 and Item1=2 tocount the number of items meeting the criteria in item1 column, and then use the DCOUNT function again with the criteria that Date is 37338 and item2=2 ...
so here is what we will get

=DCOUNT(A1:C4,"item1",F1:G2)+DCOUNT(A1:C4,"Item2",H1:I2)
resulting in 3

T H A N K S ! for keeping this discussion alive.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

What Keith does is

=SUMPRODUCT((date-range=a-date-crit)*(complete-items-range=num-crit))

where

date-range can be, say, A2:A600 and

complete-items-range can be B2:Z600 or beyond column Z which is tested for meeting a single num crit, e.g., 2.

That would make lots +DCOUNT...+DCOUNT... unless there is some short-cut to express what he succeeded to express in a single SUMPRODUCT formula. See his exchange with me.

Aladin

**keith** · Mar 24th, 2002, 03:35 PM

I am not tryting to avoid DCOUNT. The DCOUNT Function does work but it requires a formula with "DCOUNT()+DCOUNT()+DCOUNT()+DCOUNT()+DCOUNT() for all my different items (there are many) whereas with the SUMPRODUCT I can use a small formula like "=SUMPRODUCT((data!A2:A3=E2)*(data!B2:AT35=F2))".. In addition my criteia is 2 cells TOTAL verses a critera of a 2 cells minimum for every DCOUNT.. follow me? It all seems to work now - Thanks for the help.