Conditional count (two conditions)

[danlu]

Hi,

I have a range consisting of two columns, one with codes and one with dates. (That is, to each code (shown in the first column) you have a date attached (shown in the column to the right of the code). For each code I want to count the number of times this code is shown, grouped by date intervals, that is, for ex how many times the code A2B is shown within the time interval 05/01/2006 to 05/31/2006.

Thus, the result should be a table showing how many times A2B occured during january, how many times the code A2B occured during February and so on for each month and for each code.

Also this range is dynmamic, ie people can add and/or remove codes from the range.

All ideas are welcome!

[malik641]

Hi danlu, welcome to VBAX

Would it be possible to post your workbook? Or a dummy workbook (one without personal/sensitive info)?

I think I understand what you mean, but it would be much easier with a visual example

[Bob Phillips]

Assuming that you have headings, use a dynamic named range of say Codes

=OFFSET($A$1,1,,COUNTA($A:$A)-1)

and another of say Dates

=OFFSET($A$1,1,1,COUNTA($A:$A)-1)

and then use

=SUMPRODUCT(--(Codes="A2B"),--(ISNUMBER(Dates)),--(MONTH(Dates)=1))

[danlu]

See attached doc.

On tab "table" you see as an example the frequency for two codes for a certain month, that is, how often these two codes occur during May. The final table should of course include all codes and all months for each code.

On tab "rangeofvalues" you have my data.

[malik641]

Using pretty much what xld said, I used:

=SUMPRODUCT(--(Codes=B$1),--(MONTH(Dates)=ROWS($2:2)))

And copied down (from January's row).

Check out the example, be sure to look at the defined named ranges (Insert-->Name-->Define...)

[Bob Phillips]

You need to setup the names as I described, then you need to change all the dates strings to real dates, and then use

=SUMPRODUCT(--(Codes=B$1),--(TEXT(Dates,"mmmm")=$A6))

[danlu]

Hi,

I tend to get the #Value error, which I also get when I open my attached sheet where you have added the sumproduct formulas. What could be the reason (the dates are in real date format), could it have anything to do with the formatting of the formulas? What means "--" as you use in the sumproduct formula?

[mdmackillop]

Here's a sample using XLD's code
-- forces a change from True, False values to 1 and 0

[danlu]

Hi,

Must be something with my settings, because when I look in your sheet I have the #NAME? problem both in the dates column and in the table where each code per month should be summed.

[mdmackillop]

Sorry,
My fault,
I used the RandBetween function to make up some dates and this needs the Analysis Toolpack installed.
Regards
MD