Counting two variables

stephensm

New Member
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.

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Jonmo1

MrExcel MVP
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

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

MrExcel MVP
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

New Member
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

MrExcel MVP
Make sure the ranges are the same size...

stephensm

New Member
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 --?

MrExcel MVP
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

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

MrExcel MVP
Aladin, 'Study Summary'!\$E\$17 is today's date ("today()"). ISNUMBER(D16) = True. I appreciate your help.
Code:
``````=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))``````

Replies
4
Views
123
Replies
12
Views
189
Replies
2
Views
263
Replies
8
Views
66
Replies
1
Views
250

1,191,484
Messages
5,986,856
Members
440,053
Latest member
jhollingworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back