Counting two variables

[stephensm]

Hi,

I am trying to create a count based on two variables - site status and activation date - for an output of "# of sites activated this month." Column C is "Site Status" and Column D is "Activation Date." My current formula...

{=COUNT(IF(MONTH($D$16:$D$999)=MONTH('Study Summary'!$E$17), 1))}

...is "working" but is only accounting for one variable and so it is also counting sites that have been deactivated this month. I'm struggling to get the current month, driven by 'Study Summary!$E$17, inserted into a formula. Any help would be very much appreciated. Thanks.

 

[Jonmo1]

Welcome to the board...

Try this

=SUMPRODUCT(--(MONTH($D$16:$D$999)=MONTH('Study Summary'!$E$17)),--(MONTH($C$16:$C$999)="Active"))

Hope this helps.

 

[xld]

=SUMPRODUCT(--($C$16:$C$999="Active"),
--(MONTH($D$16:$D$999)=MONTH('Study Summary'!$E$17)))

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--(StatusRange=X2),--(DateRange-DAY(DateRange)+1=Y2))

where X2 houses a status value and Y2 a first day date like 1-Feb-08.

 

[stephensm]

Thanks all but I'm getting #Value! errors for each of the formulas even when I put them in as array formulas. Where did I go wrong?

 

[Jonmo1]

Make sure the ranges are the same size...

 

[stephensm]

Thanks jonmo1. I thought about that and so I copied your formula directly into the cell. Still no luck....

=SUMPRODUCT(--(MONTH($D$16:$D$999)=MONTH('Study Summary'!$E$17)),--($C$16:$C$999)="Active")

Should I be inserting something in place of the --?

 

[Aladin Akyurek]

Thanks jonmo1. I thought about that and so I copied your formula directly into the cell. Still no luck....

=SUMPRODUCT(--(MONTH($D$16:$D$999)=MONTH('Study Summary'!$E$17)),--($C$16:$C$999)="Active")

What is the value in 'Study Summary'!$E$17?

What is the name of the sheet you invoke this formula from within?

What is the result of

=ISNUMBER(D16)

Should I be inserting something in place of the --?

No...

 

[stephensm]

Aladin, 'Study Summary'!$E$17 is today's date ("today()"). ISNUMBER(D16) = True. I appreciate your help.

 

[Aladin Akyurek]

Aladin, 'Study Summary'!$E$17 is today's date ("today()"). ISNUMBER(D16) = True. I appreciate your help.

=SUMPRODUCT(
   --($C$16:$C$999)="Active"),
   --($D$16:$D$999-DAY($D$16:$D$999)+1=
         'Study Summary'!$E$17-DAY('Study Summary'!$E$17)+1))