Need Excel's Countifs "functionality" but not all Criteria Ranges have the same number of Columns

Hi Experts,

In the example file, I'm trying to come up with a formula that will allow me to count the number of cells in Columns AR:BD that match the value in Column AN, AND that have a value in Column AO.  In the attached example, I've tried to use COUNTIFS "functionality" but I can't seem to find a way to use that because not all Criteria Ranges have the same number of Columns (which is required by the COUNTIFS function.  Can someone suggest how I can get around this, perhaps with a different combination of functions?

Thanks for any insights!

Jeff

EE-Question.xlsx
LVL 2
Jeffrey SmithOwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
In AR20:
=SUMPRODUCT(($AN$8:$AN$17=$AQ20)*($AO$8:$AO$17<>"")*(LEFT($AR$8:$BD$17,1)="D"))

and similar for the other columns. Remember that when you want to test for JS or PS you need to use LEFT($AR$8:$BD$17,2)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Couple of things I can see in the formula:
- the value for Trip type is referring to cells in column BD rather than the cells in AQ
- the criteria for column AO is "" which equates to =Blank. For not blank you need "<>"

Correction for cell AR22:

=COUNTIFS($AN$8:$AN$17,$AQ22,$AO$8:$AO$17,"<>")*COUNTIF($AR$8:$BD$17,"D*")

Gives result of 100 but I think it should be 60:

15 SBT = 3
D2 = 20
3 * 20 = 60
0
Rob HensonFinance AnalystCommented:
Right, got it to equal 60 with a couple of changes elsewhere:

Formulas in AO8:AO17:

=COUNTIFS($C8:$C$508,"<>"&$C7,$C8:$C$508,C8)

Don't need the "IF Count = 0 then Blank Else Count" syntax; just count and format so zeroes don't show.

Then the summary counts can be:

=COUNTIFS($AN$8:$AN$17,$AQ22,$AO$8:$AO$17,"<>0")*COUNTIF($AR$8:$BD$17,"D*")

Updated file attached.
EE-Question-v1.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob HensonFinance AnalystCommented:
Error spotted on uploaded file, copied the formula from D* column over to others and didn't change the room type.
0
Jeffrey SmithOwnerAuthor Commented:
Thanks Rory and Ron for posting on this.  

Rory, your formula worked for all of the summary columns except for Column AS ("Single") only because both Columns AS and AT sought to count those codes that started with "S", but Column AT had "SS" before the numeric portion (which corresponds to the number of guests in each room).  I was able to solve that by modifying the summary formula in Column AS to:

=SUMPRODUCT(($AN$8:$AN$17=$AQ20)*($AO$8:$AO$17<>"")*(LEFT($AR$8:$BD$17,2)="S1"))

... because (since it is a Single room), it should only have one person (and therefore an "S1") Accommodation Code.

Ron, the correct answer in AR22, is 20, not 60 as your solution provided. I'm not sure how you determined:

15 SBT = 3
D2 = 20

... but I think that led you astray in thinking that the answer was 60.  Basically, the calculation in AR22 should just be counting the number of "D*" entries on rows 8 and 10 for the "15 SBT" Trip code (which is 20).  Thanks for pointing out that I had the wrong reference (BD20 instead of AQ20) for the comparison parameter (a casualty of looking at this, too long).  Although Rory found this and corrected same, too, I appreciate your explicitly explaining same and have awarded some points accordingly).

Thanks again, guys!
0
Jeffrey SmithOwnerAuthor Commented:
Thanks again!
0
Rob HensonFinance AnalystCommented:
I was looking at t h e two sets of condition in isolation, count of trip type AND count of room type, rather than in conjunction, ie where trip type AND room type matched.
0
Jeffrey SmithOwnerAuthor Commented:
Right.  AT the end of the day, these counts were to determine the number of rooms in use by trip - sorry if I did not make that clear.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.