GOING CRAZY! - Count Functions

[keith]

Example Data:
Date Column | Item1 Col | Item2 Col
2002/03/23 | 2 | 5
2002/03/25 | 5 | 6
2002/03/23 | 2 | 5
many more rows of similar...

Example Problem: - I need to count the number of times a "2" is listed in the "item 1" column when the date in "date column" is "2002/03/23" for the same row etc.. (2 would be above result)

I am pretty sure this is beyond simple COUNTIF and I can't seem to make DCOUNT work either. A DCOUNTIF would be perfect but it doesn't exist that I am aware of.. VBA maybe? or maybe I am missing something on COUNTIF or DCOUNT? - Please help - thanks in advance..

 

[keith]

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

 

[Yogi Anand]

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]

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]

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.

 

[keith]

LOL - me and Aladin said the same thing - must have been typing at same time

 

[Yogi Anand]

T H A N K S ! Keith and Aladin:
That settles it -- thanks for the clarification.