+ Reply to Thread
Results 1 to 3 of 3

Formual that Multiplies 2 columns to give a percentace based on 2 criteria

  1. #1
    Forum Contributor
    Join Date
    02-14-2008
    Location
    Manchester, England
    MS-Off Ver
    2007
    Posts
    110

    Formual that Multiplies 2 columns to give a percentace based on 2 criteria

    Hello all

    I've got a spreadsheet with 4 columns as follows

    Date|Hour|Value1|Value2
    19/12/1013|10:00|243|54
    19/12/2013|10:00|234|52
    19/12/2013|11:00|234|76

    What I am trying to do is multiply sum of Value2 by sum Value1 when the date criteria and hour criteria match specifics.

    If tried sumifs for selecting the multiple criteria but this will only sum 1 column and not let me multiply.

    For example in the above if my criteria where Date: 19/12/2013 and Hour: 10 I'd want to add 52 & 54 then multiply by 243 and 234 so would get 4.5 for example.

    Thanks

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formual that Multiplies 2 columns to give a percentace based on 2 criteria

    With your sample data in A1:D10
    and
    F2: the date to match....2013-12-19
    G2: the hour to match....10:00:00 AM
    Try this regular formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is that something you can work with?
    Last edited by Ron Coderre; 12-19-2013 at 10:21 AM. Reason: fixed the sample date
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formual that Multiplies 2 columns to give a percentace based on 2 criteria

    hi Dan. do you mean (243+234)/(52+54)? i don't know how you get 4.5 by multiplying. if that's so, then maybe:
    =SUMIFS(C:C,A:A,F2,B:B,G2)/SUMIFS(D:D,A:A,F2,B:B,G2)

    assumption:
    F2 is:
    19Dec2013
    G2:
    10 am

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ 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. Replies: 1
    Last Post: 01-30-2013, 12:12 PM
  2. Replies: 19
    Last Post: 01-19-2013, 04:27 AM
  3. [SOLVED] Lookup with two criteria-formual not working
    By Excel Dumbo in forum Excel General
    Replies: 2
    Last Post: 11-12-2012, 05:11 PM
  4. how to add additional criteria to a SUMIF formual?
    By jgomez in forum Excel General
    Replies: 5
    Last Post: 07-15-2011, 10:16 AM
  5. Replies: 7
    Last Post: 05-09-2008, 10:13 AM

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