sumproduct

[tabspace]

I have the following data:

I would like to use sumproduct based on day of the week but also replacing the Hi, Mid and Lo with their correct values.

For example, for Paul, the total of his Mondays, should be 1*100+3*1+1*1

Any ideas?

 

[Jonmo1]

Re: sumproduct - advanced difficulty

If you Sort the table in H2:I4 in Ascending order by column H ( so it goes hi lo mid)

Try this array formula entered with CTRL + SHIFT + ENTER

=SUM(($B$1:$F$1="Monday")*LOOKUP(B3:F3,$J$2:$K$4)*$B$2:$F$2)

 

[barry houdini]

Re: sumproduct - advanced difficulty

If you use SUMIF you can leave the lookup table as it is, i.e.

=SUMPRODUCT((B$1:F$1="Monday")+0,B$2:F$2,SUMIF(H$2:H$4,B3:F3,I$2:I$4))

 

[tabspace]

Re: sumproduct - advanced difficulty

Sorry Jonmo1this didn't help. Thanks anyway!

 

[tabspace]

Re: sumproduct - advanced difficulty

Hi Barry,

This worked perfectly well! Can you explain to me why the +0? If I remove it, the formula doesn't work anymore.

Thanks again!

If you use SUMIF you can leave the lookup table as it is, i.e.

=SUMPRODUCT((B$1:F$1="Monday")+0,B$2:F$2,SUMIF(H$2:H$4,B3:F3,I$2:I$4))

 

[Jonmo1]

Re: sumproduct - advanced difficulty

It looks like I changed the location of the lookup table from your post..

=SUM(($B$1:$F$1="Monday")*LOOKUP(B3:F3,$J$2:$K$4)*$B$2:$F$2)

According to the picture in your post, That should have been
=SUM(($B$1:$F$1="Monday")*LOOKUP(B3:F3,$H$2:$I$4)*$B$2:$F$2)

Also remember it's an array formula that requires a special key combination to enter it.
Instead of just pressing Enter, you press CTRL + SHIFT + ENTER
You'll know it's correctly entered when the formula is enclosed in {brackets}

Here's how it should look

Book1
	A	B	C	D	E	F	G	H	I
1		Monday	Monday	Tuesday	Friday	Monday		lookuptable
2	Incentive day	1	3	2	4	1		hi	100
3	Paul	Hi	Lo	Hi	Mid	Lo	104	lo	1
4	Peter	Mid	Hi	Lo	HI	Mid	400	mid	50
5	Susan	Mid	Lo	Hi	Hi	Hi	153
Sheet1

Cell Formulas
Range	Formula
G3	{=SUM(($B$1:$F$1="Monday")*LOOKUP(B3:F3,$H$2:$I$4)*$B$2:$F$2)}
G4	{=SUM(($B$1:$F$1="Monday")*LOOKUP(B4:F4,$H$2:$I$4)*$B$2:$F$2)}
G5	{=SUM(($B$1:$F$1="Monday")*LOOKUP(B5:F5,$H$2:$I$4)*$B$2:$F$2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jonmo1]

Re: sumproduct - advanced difficulty

And come to think of it, change SUM to SUMPRODUCT and it no longer requires the Array Entry
=SUMPRODUCT(($B$1:$F$1="Monday")*LOOKUP(B3:F3,$H$2:$I$4)*$B$2:$F$2)

 

[tabspace]

Re: sumproduct - advanced difficulty

Thanks Jonmo1! For some reason I can't get your formula to work

I get N/A

 

[barry houdini]

Re: sumproduct - advanced difficulty

Can you explain to me why the +0? If I remove it, the formula doesn't work anymore.

Using this approach

=SUMPRODUCT((B$1:F$1="Monday")+0,B$2:F$2,SUMIF(H$2:H$4,B3:F3,I$2:I$4))

The (B$1:F$1="Monday") part returns an array of TRUE/FALSE values. For SUMPRODUCT to work as required we need those to be 1/0 values so using +0 is one way to make that conversion - an alternative is to multiply some of the conditions in the formula, e.g. with this revised syntax

=SUMPRODUCT((B$1:F$1="Monday")*SUMIF(H$2:H$4,B3:F3,I$2:I$4),B$2:F$2)

 

[tabspace]

Re: sumproduct - advanced difficulty

Thanks for the explanation!

Using this approach

=SUMPRODUCT((B$1:F$1="Monday")+0,B$2:F$2,SUMIF(H$2:H$4,B3:F3,I$2:I$4))

The (B$1:F$1="Monday") part returns an array of TRUE/FALSE values. For SUMPRODUCT to work as required we need those to be 1/0 values so using +0 is one way to make that conversion - an alternative is to multiply some of the conditions in the formula, e.g. with this revised syntax

=SUMPRODUCT((B$1:F$1="Monday")*SUMIF(H$2:H$4,B3:F3,I$2:I$4),B$2:F$2)