Countifs...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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))
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query - add records automatically.. 2
Transpose rows and columns 3
Pivot table calculated field Count(field) / sum(field) 0
Qry returning too many records - join question 4
sumproduct to calculate total based on non-continuous columns? 1
how to count#cells w/= value in other column and not count blank c 4
Help with Date Query and Report 4
Query to display highest totals 1

Back
Top