+ Reply to Thread
Results 1 to 5 of 5

Calculating Sum of count of 2 variables squared per row

  1. #1
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Calculating Sum of count of 2 variables squared per row

    In each row I have 6 values, either an A or an R. I have 19 rows. In each row I want to count "A" and square it, and add that to the count of R squared. I can do it row by row (see column H attached) but want to be able to come up with the final sum (418) directly. Every countif and Sumproduct I've tried doesn't come close.
    Attached Files Attached Files
    Last edited by ChemistB; 10-08-2015 at 04:08 PM.
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Calculating Sum of count of 2 variables squared per row

    with this udf maybe


    Please Login or Register  to view this content.
    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating Sum of count of 2 variables squared per row

    Try this...

    =SUM(MMULT(--(A2:F20="A"),{1;1;1;1;1;1})^2)+SUM(MMULT(--(A2:F20="R"),{1;1;1;1;1;1})^2)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Calculating Sum of count of 2 variables squared per row

    Thanks to both of you. Both excellent suggestions.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating Sum of count of 2 variables squared per row

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Cost with Variables
    By Urdnot Wr3x in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2015, 10:29 PM
  2. Calculating values based on variables
    By thomasham in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-02-2014, 02:37 PM
  3. Calculating Variables
    By NoCalCal in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2012, 02:10 AM
  4. Calculating Streaks with more than 2 variables
    By poko10 in forum Excel General
    Replies: 2
    Last Post: 12-05-2011, 08:51 AM
  5. Calculating on multiple variables
    By DubboPete in forum Excel General
    Replies: 1
    Last Post: 04-19-2006, 12:38 AM
  6. linest r-squared bug when calculating forced through zero?
    By Filtration Guy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-13-2005, 09:50 PM
  7. Calculating Percentages with Variables
    By tssjhs in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2005, 10:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1