Sumproduct

[Zot]

Discussion split from this thread: ADD A RANGE OF CELLS FROM MULTIPLE COLUMNS BASED ON CRITERIA FROM ONE COLUMN

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.


Try

=SUMPRODUCT((F5564:F5568="CC")*EL5564:EP5568)

This formula works in 365? This is good. I'm still in 2016

 

[Peter_SSs]

This formula works in 365? This is good. I'm still in 2016

Try Ctrl+Shift+Enter

 

[Zot]

Try Ctrl+Shift+Enter

No. It did not work. Get #VALUE!

 

[Peter_SSs]

No. It did not work. Get #VALUE!

Sorry, I don't have 2016 to test with.

Here is mine

21 01 06.xlsm
	C	D	E	F																																																																																																																																							EK	EL	EM	EN	EO	EP
5565	45			CC																																																																																																																																								2	6	9	4	1
5566				DD																																																																																																																																								9	3	5	5	7
5567				CC																																																																																																																																								2	8	6	5	2
5568
Sumproduct

Cell Formulas
Range	Formula
C5565	C5565	=SUMPRODUCT((F5564:F5568="CC")*EL5564:EP5568)

 

[Zot]

Sorry, I don't have 2016 to test with.

SumProduct works if array is single column but not across several columns (2 dimensional) without Ctrl+Shift+Enter since it is an array formula. I have no idea 365 can do that

 

[Peter_SSs]

SumProduct works if array is single column but not across several columns (2 dimensional) without Ctrl+Shift+Enter since it is an array formula.

It seemed to work across multiple columns back in 2012 well before excel 2016:

formula based on date

hi i have been struggling for a couple of hours now. what i would like is a formula in Cell B1 of my finance spreadsheet which looks for the current date in column a and sum's the values in the corresponding columns (which are also forumlas) Here is a mini version: So today the bank balance...

www.mrexcel.com

Do you have any text or error values in EL5564:EP5568?

 

[Zot]

Here I recreated a sample

Book1.xlsm
	C	D	E	F	G
15	CC		100	0	0
16	CA		0	100	0
17	CC		0	0	100
18
19			#VALUE!
20			#VALUE!
Sheet8

Cell Formulas
Range	Formula
E19	E19	=SUMPRODUCT(C15:C17="CC",E15:G17)
E20	E20	=SUMPRODUCT(C15:C17="CC",E15:G17)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Zot]

I see. It works if use like your formula =SUMPRODUCT((C15:C17="CC")*E15:G17)

Cannot use like regular SumProduct with just comma

 

[Peter_SSs]

Those are not in the same format as my formula. Look more closely.

 

[Peter_SSs]

I see. It works if use like your formula =SUMPRODUCT((C15:C17="CC")*E15:G17
Cannot use like regular SumProduct with just comma

Sorry, I missed that post when making my last one. No you cannot, because SUMPRODUCT can only work with numbers, not TRUE/FALSE values which must be coerced to numbers.
You could use commas like this

=SUMPRODUCT(--(C15:C17="CC"),E15:G17)