Excel 2003 SUMPRODUCT question

[alright-stuff]

Using excel 2003 I'm trying to count from a small table of data, the number of times certain occurrences happen within a date range. I am referring to 3 cells.

cell a1=start date
cell a2 =finish date
cell a3 =session (am or pm or all)


When using SUMPRODUCT I can count AM between certain dates and also PM between dates using:

...SUMPRODUCT((H11:H65336>=A1)*(H11:H65336<=A2)*(C11:C65336=A3))

I want to count AM and PM separately but will also need to count AM & PM together but cell A3 has a list validation dropdown with AM,PM and ALL as selections.

Also the column of which has the data only accepts AM or PM as a value, ALL in NOT a selection. I was thinking of using a criteria as any cell with a M in it but I understand wildcard characters can't be used in 2003.

The question is how do I capture occurrences when using my input cells if I select a date range from A1 and A2, and then I select ALL from A3? How does it capture both AM and PM occurrences matching my other criteria.

 

[T. Valko]

Using excel 2003 I'm trying to count from a small table of data, the number of times certain occurrences happen within a date range. I am referring to 3 cells.

cell a1=start date
cell a2 =finish date
cell a3 =session (am or pm or all)


When using SUMPRODUCT I can count AM between certain dates and also PM between dates using:

...SUMPRODUCT((H11:H65336>=A1)*(H11:H65336<=A2)*(C11:C65336=A3))

I want to count AM and PM separately but will also need to count AM & PM together but cell A3 has a list validation dropdown with AM,PM and ALL as selections.

Also the column of which has the data only accepts AM or PM as a value, ALL in NOT a selection. I was thinking of using a criteria as any cell with a M in it but I understand wildcard characters can't be used in 2003.

The question is how do I capture occurrences when using my input cells if I select a date range from A1 and A2, and then I select ALL from A3? How does it capture both AM and PM occurrences matching my other criteria.

If there are no empty cells in column C and the only possible entries are either AM or PM then you really don't need to count column C when "ALL" is selected. You can just count the dates that fall within the date boundaries.

Try something like this:

=IF(A3="ALL",SUMPRODUCT(--(H11:H65336>=A1),--(H11:H65336<=A2)),SUMPRODUCT(--(H11:H65336>=A1),--(H11:H65336<=A2),--(C11:C65336=A3)))