# 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
###### Who is Participating?

x
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.

Commented:
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)

Experts Exchange Solution brought to you by

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

Finance 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
Finance 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
Finance AnalystCommented:
Error spotted on uploaded file, copied the formula from D* column over to others and didn't change the room type.
OwnerAuthor 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!
OwnerAuthor Commented:
Thanks again!
Finance 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.
OwnerAuthor 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.
###### 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.