Counif(s) Help..... (1 Viewer)

[TajikBoy]

Good morning all,

Need some assistance for the attached file....

I need to count the "X"s in Villa Occupancy sheet based on villa number and date ranges, for example, for January 1 to 31, villas 101 to 122, total of "X"s.... Both criteria sets are in the Summary sheet for linking.

Been trying for few hours but not getting any where unfortunately.....

Many thanks in advance and wishing you all a belated happy new year !

 

[arnelgp]

i use vba to get the counts.

 

[TajikBoy]

i use vba to get the counts.

So simple yet elegant solution.......

Many thanks arnelgp, much appreciated

 

[cheekybuddha]

You can also do this without VBA, which is sometimes important where company policy forbids VBA.

You can place the following formula in cell H7 on the Summary sheet:

=IFERROR(SUMPRODUCT(--(INDIRECT("'Villa Occupancy'!" & ADDRESS(MATCH($E7, 'Villa Occupancy'!$B$1:$B$108, 0), MATCH(H$4, 'Villa Occupancy'!$A$6:$APX$6, 0)) & ":" & ADDRESS(MATCH($F7, 'Villa Occupancy'!$B$1:$B$108, 0), MATCH(OFFSET(H$4,1,0), 'Villa Occupancy'!$A$6:$APX$6, 0))) = "X")), 0)

Then, you need to change the formula in cell H8 because you used an absolute reference:

=IFERROR(H7/($D7*H$3),0)

Before it was: =IFERROR(H7/($D$7*H$3),0)

Then select both cells H7 and H8. Using the black cross drag handle at the bottom right, drag over to column S.

Then drag all down to row 18.

Dragging two rows together will make sure the formulas adjust for the correct rows.

The main formula is quite complicated, so here it is formatted to try and make it easier to understand:

=IFERROR(
  SUMPRODUCT(
    --(
      INDIRECT(
        "'Villa Occupancy'!" &
        ADDRESS(
          MATCH(
            $E7,
            'Villa Occupancy'!$B$1:$B$108,
            0
          ),
          MATCH(
            I$4,
            'Villa Occupancy'!$A$6:$APX$6,
            0
          )
        ) &
        ":" &
        ADDRESS(
          MATCH(
            $F7,
            'Villa Occupancy'!$B$1:$B$108,
            0
          ),
          MATCH(
            OFFSET(I$4,1,0),
            'Villa Occupancy'!$A$6:$APX$6,
            0
          )
        )
      ) = "X"
    )
  ),
  0
)

See attached.

 

[TajikBoy]

You can also do this without VBA, which is sometimes important where company policy forbids VBA.

You can place the following formula in cell H7 on the Summary sheet:

=IFERROR(SUMPRODUCT(--(INDIRECT("'Villa Occupancy'!" & ADDRESS(MATCH($E7, 'Villa Occupancy'!$B$1:$B$108, 0), MATCH(H$4, 'Villa Occupancy'!$A$6:$APX$6, 0)) & ":" & ADDRESS(MATCH($F7, 'Villa Occupancy'!$B$1:$B$108, 0), MATCH(OFFSET(H$4,1,0), 'Villa Occupancy'!$A$6:$APX$6, 0))) = "X")), 0)

Then, you need to change the formula in cell H8 because you used an absolute reference:

=IFERROR(H7/($D7*H$3),0)

Before it was: =IFERROR(H7/($D$7*H$3),0)

Then select both cells H7 and H8. Using the black cross drag handle at the bottom right, drag over to column S.

Then drag all down to row 18.

Dragging two rows together will make sure the formulas adjust for the correct rows.

The main formula is quite complicated, so here it is formatted to try and make it easier to understand:

=IFERROR(
  SUMPRODUCT(
    --(
      INDIRECT(
        "'Villa Occupancy'!" &
        ADDRESS(
          MATCH(
            $E7,
            'Villa Occupancy'!$B$1:$B$108,
            0
          ),
          MATCH(
            I$4,
            'Villa Occupancy'!$A$6:$APX$6,
            0
          )
        ) &
        ":" &
        ADDRESS(
          MATCH(
            $F7,
            'Villa Occupancy'!$B$1:$B$108,
            0
          ),
          MATCH(
            OFFSET(I$4,1,0),
            'Villa Occupancy'!$A$6:$APX$6,
            0
          )
        )
      ) = "X"
    )
  ),
  0
)

See attached.

Thanks CheekyBuddha - This is also very nice, if I can add an extra question to your formula, if I want to change the "x" to numbers and add up in the same fashion so to speak? Possible?

 

[cheekybuddha]

Possible?

Probably!

You would have to amend the formula slightly because at the moment it just counts whether a cell contains the search term. It does this by using the unary operator -- to return 1 or 0 if the expression is true or false for each cell in the range.

If you need to sum all the numbers in the range I imagine that you can remove the operator, but without data to test and a more specific brief of what you need I can't be sure!

 

[TajikBoy]

Probably!

You would have to amend the formula slightly because at the moment it just counts whether a cell contains the search term. It does this by using the unary operator -- to return 1 or 0 if the expression is true or false for each cell in the range.

If you need to sum all the numbers in the range I imagine that you can remove the operator, but without data to test and a more specific brief of what you need I can't be sure!

That's cool, If you don't mind, I will work on the format, add in the revenue sheet and related and flip it over to you? Your next coffee(s) on me

 

[cheekybuddha]

If you don't mind, I will work on the format, add in the revenue sheet and related and flip it over to you?

That's fine - there's plenty of folk here who can advise.

You should also see if you can apply the principles of the solution above yourself first.

If you need an explanation of the formula then let us know and I will post something when I have more time later (if someone else doesn't do so before me).