CODEWITHSUNDEEP
excelcellcountif
user3392350
user3392350

Reputation: 1

Count the numbers of members meeting two criteria?

How do you count the numbers of members in my table?

The table is called Table2. I would like to see the amount of people that meet the criteria of having the membership code of A3 (changing when it moves down a cell) and pay by either Annual Membership 1, or Annual membership 2.

Upvotes: 0

Views: 166

Answers (1)

whydoesntwork
whydoesntwork

Reputation: 131

I can recommend three ways of going about this, in descending order of preference.

  1. Use a pivot table to summarise the data into a cross table, if there is a new type of payment code it automatically be reflected, you don't need to do complex formulae, only make sure the input range used for the pivot table includes all the data. You need to manually refresh the Pivot table.
  2. Use the sumifs() function (you can sum a cell with 1 as value), research a bit on multiple conditions, there is plenty of info out there (apparently sumifs() supports up to 29), I can suggest this one
  3. Sometimes I just use the formula =B1&"-"&C1&"-"D1 (into a fourth E column) to combine the two or more cells I want to test into a single text value and then do the countif() or sumif(). It is quick and dirty but can be very easy to understand and has the benefit that at each row you have some value you could filter for using the data filter functionality. You can also use an OR() and IF() to create the condition at each row that would yield "True" or other value that is more meaningful (e.g. If(And(A1="A3",Or(B1="Membership 1",B1="Membership 2")),"VIP","Regular"), then use the countif() at the bottom to count the VIPs.
  4. You can use also array formulas but I found them difficult to maintain and I don't have enough real life experience of using it.

Note: I didn't test the formulae, this is illustrative to give you a start, my advise is you use pivots if you only need a summary reporting.

Good luck!

Upvotes: 1

Related Questions