Results 1 to 8 of 8

SUM with criteria and product for each value

Threaded View

  1. #1
    Registered User
    Join Date
    04-02-2015
    Location
    Rome, Italy
    MS-Off Ver
    Office 2007
    Posts
    3

    SUM with criteria and product for each value

    I need a table with the sums of the values from a column depending on the way they respond to certain criteria. It would have been fine using SUMIFS but the problem comes for those values that respond to different criteria at the same time and for this reason they need to be split.

    I found a formula that works for a couple of values but not one to give a sum for many values.

    I was probably not very clear. It is better that I explain it with the example from the spreadsheet attached hereto.

    In the chart "Income" I want to have the sum of the values coming from the columns Total Price divided in different cells depending on the origin of the client (World OR ITA) and the season each reservation falls in (Low Season, High Season, Very High Season).

    For example I was using =SUMIFS(C7:C11;G7:G11;"ITA";E7:E11;">=1") to put the right values in the cell J8.

    The problems come for those values from the column C that needs to be split in different cells of the table Income, like the €379 from C9. That reservation has nights in different seasons. In this case for example I need to sum 3/5*379 in K8 (World and HS) and 2/5 in K9 (World and VHS).

    I know how to do it for this few values (see formula below) but I need something that works like the SUM formula above in order to be able to add new lines and see immediately the results automatically changing in the table income, even if the value added needs to be split in different cells.

    =IF(AND(H7="World";E7<>"");C7*E7/G7;0)+IF(AND(H8="World";E8<>"");C8*E8/G8;0)+IF(AND(H9="World";E9<>"");C9*E9/G9;0)+IF(AND(H10="World";E10<>"");C10*E10/G10;0)

    Thank you all for your help!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUM IF (criteria) then PRODUCT
    By manywise in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 09-04-2014, 07:01 PM
  2. Product if 3 criteria
    By ygoksel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-04-2014, 06:45 AM
  3. Sum Product with Two Criteria
    By lel33 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-02-2012, 01:37 PM
  4. Replies: 1
    Last Post: 01-14-2012, 12:00 PM
  5. Product with Criteria
    By axed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-29-2010, 06:14 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