Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Count Occurences Of Two Variables

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Count Occurences Of Two Variables

    I have been given a spreadsheet to calculate. I think the data is very badly laid out but I cant change it.

    I have attached a sample from it. The original has 1000 people (accross row 1 in multiple sheets) and has approx 200 rows.

    I only have to calculate sheets individually.

    I need to count all occurences in the row for;
    Current Level = 1 and Training Requirement = 1 (Cell B4)
    Current Level = 1 and Training Requirement = 2 (Cell C4)
    etc for all Currant Level 1-4 and Training Requirement 1-3.

    I have been looking over how to use Sumproduct but I [email protected] see how to use it here.

    Any help greatly appreciated as I don't want to do this manually.
    Attached Files

  • #2
    Re: Count Occurences Of Two Variables Accross A Row

    Maybe
    =SUMPRODUCT(($N$3:$Y$3="Current level")*($N$4:$Y$4=1)*($O3:$Z3="Training Requirement")*($O4:$Z4=B$3))

    see attached.
    Attached Files
    locii Insight
    Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

    Comment


    • #3
      Re: Count Occurences Of Two Variables Accross A Row

      This seems to work for your example. Put this formula in B4, adjust the absolute cell reference to get the end of your data. Note one set is offset from the other two. Drag to the right. The current level value then needs to be reset for each current level.

      Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas

      =SUMPRODUCT((MOD(COLUMN($N$4:$X$4),2)=0)*($N$4:$X$4=1)*($O$4:$Y$4=B3))
      Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

      Comment


      • #4
        Re: Count Occurences Of Two Variables Accross A Row

        Hi,

        In B4 and copie dacross,

        =SUMPRODUCT(--($N3:$X3&" "&$N4:$X4=LOOKUP("zzzzz",$B$1:B$1)),--($O4:$Y4=B$3))

        HTH
        Kris

        ExcelFox

        Comment


        • #5
          Re: Count Occurences Of Two Variables Accross A Row

          Firstly, carlmack, Derk and Krishnakumar
          thank you very much for your work.

          At first look this is beyond me.
          I'm going to have to spend some time trying to understand what each solution is doing.

          Thanks again.

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X