Countifs...

A

ajayb

Hi all

I need to count:
The total number of new placements (ie only count the 'Yes' in column B)
The total number of active placements out of those new placements (ie only
count the 'Yes' in column C if column B is also 'Yes')

Column A Column B Column C Column D
Row 1 Placement New Active Count as active
Row 2 1 Yes Yes Yes
Row 3 2 Yes No No
Row 4 3 No Yes No
Row 5 4 No No No

So in the above example I ignore placements 3 and 4 because they are not
'New' placements (column B = No), so total placements = 2
Out of those 2 remaining, only 1 is currently active.

Have tried the following -

CountActive: Sum(IIf([New]=Yes,1,0))+Sum(IIf([Active]=Yes,1,0))

But that also counts row 4. Can anyone help me out on this?

Thanks
 
J

John Spencer

Assumption New and Active are boolean (yes/no) fields

CountActive: Abs(Sum(New And Active))

Changing your posted expression.

CountActive: Sum(IIf([New]=Yes AND [Active]=Yes,1,0))
 
A

ajayb

Thanks John, that worked a treat.

Andy

John Spencer said:
Assumption New and Active are boolean (yes/no) fields

CountActive: Abs(Sum(New And Active))

Changing your posted expression.

CountActive: Sum(IIf([New]=Yes AND [Active]=Yes,1,0))


ajayb said:
Hi all

I need to count:
The total number of new placements (ie only count the 'Yes' in column B)
The total number of active placements out of those new placements (ie
only
count the 'Yes' in column C if column B is also 'Yes')

Column A Column B Column C Column D
Row 1 Placement New Active Count as active
Row 2 1 Yes Yes Yes
Row 3 2 Yes No No
Row 4 3 No Yes No
Row 5 4 No No No

So in the above example I ignore placements 3 and 4 because they are not
'New' placements (column B = No), so total placements = 2
Out of those 2 remaining, only 1 is currently active.

Have tried the following -

CountActive: Sum(IIf([New]=Yes,1,0))+Sum(IIf([Active]=Yes,1,0))

But that also counts row 4. Can anyone help me out on this?

Thanks
Click to expand...
Click to expand...
 
Top