Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Count Occurences Of Two Variables

  1. #1
    Join Date
    22nd March 2007
    Location
    Dublin
    Posts
    52

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th January 2005
    Location
    Cyprus
    Posts
    2,252

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,528

    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.

  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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

  5. #5
    Join Date
    22nd March 2007
    Location
    Dublin
    Posts
    52

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Count Occurences Of Value Chosen From Drop Down
    By kevinm12 in forum Excel General
    Replies: 3
    Last Post: September 13th, 2007, 17:41
  2. cmp 2 cols and assign occurences count
    By Excel_VBA_kid in forum Excel General
    Replies: 3
    Last Post: March 16th, 2005, 14:57
  3. IF condition, count occurences
    By Excel_VBA_kid in forum Excel General
    Replies: 7
    Last Post: January 29th, 2005, 22:41
  4. Formula To Count Occurences of Letter.
    By ExcelIdiot in forum Excel General
    Replies: 3
    Last Post: October 26th, 2004, 23:28
  5. Replies: 3
    Last Post: April 9th, 2004, 19:55

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno