Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 79
Default Using SUMIF inside SUMPRODUCT = complex problem (suite)

This post is an evolution of the one I posted this morning.

I have an excel model quite complex with the followinf layout
(somehow simplified):

col A col B col C col D Col E Col F
row 1: a 3 a 0.5 a 2
row 2 b 4 b 1 b
3
row 3: a 5

row 5: 1

I need to calculate, by means of a sumproduct function, the following:

if cell B5 contains 1:
(3*0.5 + 4*1 + 5*0.5) = 1.5 + 4 + 2.5 = 8

if cell B5 contains 2:
(3*2 + 4*3 + 5*3) = 6 + 12 + 15 = 33

Cell B5 changes very often

Following barry Houdini's suggestion, I have tried

=SUMPRODUCT(B1:B3,CHOOSE(B5,SUMIF(C1:C2,A1:A3,D1:D 2),SUMIF
(E1:E2,A1:A3,F1:F2))

.... which happens to work, but only if I use Ctrl-Shift-Enter.

Is there anyway to get rid of the Ctrl-Shift-Enter?

Because my model is really complex, I do not want to use auxiliary
columns for intermediate results.

Is it possible to achieve the desired result?

Thank you again
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Using SUMIF inside SUMPRODUCT = complex problem (suite)

I get a different result whe B5=2.

If B5=2 I get 28

Try it like this:

=SUMPRODUCT(B1:B3,LOOKUP(A1:A3,IF(B5=1,C1:D2,E1:F2 )))

No error checking!

It assumes that B5 will be either 1 or 2. Also, it assumes that your data in
C1:C2 and E1:E2 is sorted in ascending order as is demonstrated in your
sample data.

--
Biff
Microsoft Excel MVP


"vsoler" wrote in message
...
This post is an evolution of the one I posted this morning.

I have an excel model quite complex with the followinf layout
(somehow simplified):

col A col B col C col D Col E Col F
row 1: a 3 a 0.5 a 2
row 2 b 4 b 1 b
3
row 3: a 5

row 5: 1

I need to calculate, by means of a sumproduct function, the following:

if cell B5 contains 1:
(3*0.5 + 4*1 + 5*0.5) = 1.5 + 4 + 2.5 = 8

if cell B5 contains 2:
(3*2 + 4*3 + 5*3) = 6 + 12 + 15 = 33

Cell B5 changes very often

Following barry Houdini's suggestion, I have tried

=SUMPRODUCT(B1:B3,CHOOSE(B5,SUMIF(C1:C2,A1:A3,D1:D 2),SUMIF
(E1:E2,A1:A3,F1:F2))

... which happens to work, but only if I use Ctrl-Shift-Enter.

Is there anyway to get rid of the Ctrl-Shift-Enter?

Because my model is really complex, I do not want to use auxiliary
columns for intermediate results.

Is it possible to achieve the desired result?

Thank you again



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using VLOOKUP inside SUMPRODUCT = complex problem vsoler Excel Worksheet Functions 2 August 29th 09 07:25 PM
Problem with SUMPRODUCT(SUMIF(INDEX formula linking to external fi gina1221 Excel Worksheet Functions 3 January 14th 09 03:53 PM
Complex SUMPRODUCT/VLOOKUP/SUMIF tigger Excel Worksheet Functions 1 August 28th 08 04:30 PM
SUMIF and SUMPRODUCT with INDIRECT formula problem Sunnyskies Excel Discussion (Misc queries) 1 August 18th 07 11:17 AM
SUMIF Function Inside SUMPRODUCT Function Abdul Waheed Excel Worksheet Functions 17 September 19th 05 04:24 PM


All times are GMT +1. The time now is 10:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"