Forum Discussion

Anne Heij's avatar
Anne Heij
Copper Contributor
Oct 12, 2017

How to count multiple items?

I have a worksheet (see preview) in which I am searching for a specific study in a different tab. 

The study can be mentioned in column N and column Q. Therefore I am using the formula count.if (=AANTALLEN.ALS('IT Talent 2018'!$M$2:$Q$1000;"Cyber Security")). This is working all right and it counts the actual amount of this specific study. 

However, for the next searching criteria I am searching for hbo as well, so I added that one to the formula (AANTALLEN.ALS('IT Talent 2018'!$M$2:$Q$1000;"Cyber Security";'IT Talent 2018'!$J$2:$N$1000;"hbo*")), but this one does not appear to be working since there is one person who complies to this searching criteria. 

 

 

What am I doing wrong? 

Formulas & Functions
Show and Tell

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 12, 2017

    Hi Anne,

     

    Both ranges shall be the same size. Your first range is from M to Q (4 columns), second one from J to N (5 columns)

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 12, 2017

        Perhaps something like this

        =SUMPRODUCT(
   (OFFSET('IT Talent 2018'!$M$6:$M$1000,0,1,,5)="Cyber Security")*
   (LEFT(OFFSET('IT Talent 2018'!$J$6:$J$1000,0,1,,5),3)="hbo")
)

        Formula is in A1 of the attached file not translate manualy into your regional settings, copy/paste it in your file. But i didn't test combinations.

Resources