CODEWITHSUNDEEP
excel-formula
pita
pita

Reputation: 59

Excel - Sumproduct with 2 conditions

Thanks to the community a couple of days ago I am now using the following formula to count the number of rooms that belong to a given room type.

=SUMPRODUCT(COUNTIF(R2:R3000,Z2:Z300))

This is working correctly however our Property Management system is including some erroneous data that is adding additional records that do not need to be counted. The 2nd condition I want to add is:

Check the value in column T (room nights) to ensure that is >= 1.

From other posts I can see that you need to multiply the 2 conditions but haven't got this to work. Do I need just the 1 or 2 countif functions?

Upvotes: 0

Views: 118

Answers (1)

user4039065
user4039065

Reputation:

Converting the COUNTIF function to a COUNTIFS function should be sufficient.

=SUMPRODUCT(COUNTIFS(R2:R3000, Z2:Z300, T2:T3000, ">"&0))

Upvotes: 1

Related Questions