Countifs with multiple ranges (for experts)

**Habim** · 02-08-2017, 12:39 PM

Hello excel experts,

My issue is the following: I am working on a seating plan in Excel where each seat is represented by three cells on three rows. First cell is seat number, below the guest group it is allocated to and finally the lounge/hospitality that is linked to the seat.

Now, what I would like to do is use a countifs formula to count how many seats I have per guest group AND per lounge/hospitality (example: out of the 25 seats given to "Sponsors", 5 are in the "Gold" lounge ("S" in cells 1-5 row 2 and "G" in cells 1-5 row 3) and 20 are in the "Bronze" lounge ("S" in cells 6-25 row 2 and "B" in cells 6-25 row 3).

Currently I can solve it for single ranges with Countifs(row 2;"S";row 3;"G") but I cannot manage to do the same for multiple ranges. The seating plan contains 30 rows of seats (thus 90 rows of cells). If anyone has a better solution than doing multiple countifs one after the other that would be an extreme help!

Thanks in advance for the investigation!

**JohnTopley** · 02-08-2017, 12:51 PM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on "Reply" then GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Habim** · 02-08-2017, 01:48 PM

Thank you for the tip, I attached a sample in the first post.

**JohnTopley** · 02-08-2017, 03:28 PM

in M94

=SUMPRODUCT(($B$71:$AC$89=M$93)*(MOD(ROW($A$71:$AC$89),3)=2)*($B$70:$AC$88=$J94)*(MOD(ROW($A$70:$AC$88),3)=1))

in O94

=SUMPRODUCT(($B$71:$AC$89=O$93)*(MOD(ROW($A$71:$AC$89),3)=2)*($B$70:$AC$88=$J94)*(MOD(ROW($A$70:$AC$88),3)=1))

Copy both down

**Phuocam** · 02-08-2017, 07:22 PM

Another way ...

M94:

=COUNTIFS($B$70:$AC$88,$J94,$B$71:$AC$89,M$93)

O94:

=COUNTIFS($B$70:$AC$88,$J94,$B$71:$AC$89,O$93)

Countifs with multiple ranges (for experts)

LinkBack

Thread Tools

Rate This Thread

Display

Countifs with multiple ranges (for experts)

Re: Countifs with multiple ranges (for experts)

Re: Countifs with multiple ranges (for experts)

Re: Countifs with multiple ranges (for experts)

Re: Countifs with multiple ranges (for experts)

Thread Information

Users Browsing this Thread

Similar Threads

SUMIFS/COUNTIFS for multiple conditions/ranges

SUMIFS/COUNTIFS for multiple conditions/ranges

[SOLVED] Help with Countifs in multiple ranges & multiple criteria

Countifs with multiple variables and date ranges

COUNTIFS with multiple non-consecutive ranges

MS Office 2010: Need help with CountIfs function, multiple ranges and a date as criteria

[SOLVED] Problem using COUNTIFS with multiple conditions and named ranges

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions