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!

Thank you for the reply.

In this case the result of K8 should be 227,40€ (value 379€ only for its 3/5 goes in K8, the other 2/5 goes in K9).

I would need a formula that will update cell K8 everytime I add a new line in the table to the left.

For example (line 11 in red in the new spreadheet attached hereto) if I add a reservation with 100€ of Total Price, 4 nights total all in High season and origin World....the new result in K8 should be automatically 327,40€.

Originally Posted by benishiryo

hi lodone. i suggest you type out manually a few answers in your desired results next time. that helps us ascertain we are on the right track. i am guessing you can try this array formula in J7:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

copy to the rest of the cells.

First thing, thank you guys for your answers!

I tried this formula but even pressing CTRL+SHIFT+ENTER the arrays do not appear and I get an error.

Originally Posted by bebo021999

Hi
It seems column B values are total of D,E and F column, below formula and also benishyrio's works:

Please Login or Register  to view this content.

If not,

Please Login or Register  to view this content.

Put it in J7 then drag down and accross

I tried the first formula and it works perfectly, giving back the results I expect.

There is only one problem and it probably depends by my lack of knowledge about excel world.

I don't find the way to make it work adding new lines without changing every time the size of the matrix manually.

The only way I found is to add an extra empty line on the top of the table on the left with just a 1 in column B to avoid the error #DIV/0.
In this way I can extend the matrix including also this line giving me the possibility to add new lines below that one and having all the results updated autmatically.

Of course I bet there is for sure a more elegant solution to solve this little issue...