Sumproduct not correct

[Tigerexcel]

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	ID no.	Course	Unit1	Unit2	Unit3	Unit4	Unit5	Unit6	Unit7	Unit8	Countif Credits	Sumproduct Credits		Credits given
2	ID 1	Diploma	ABC2020 56-P	ABC2040 28-N	ABC1110 30-N	ABC1090 61-C	ABC2690 61-C	ABC2090 28-N	ABC1110 50-P	ABC1040 61-C	2	0		Diploma	ABC2*P	ABC2*C	ABC2*D
3	ID 2	Certificate	ABC2020 36-N	ABC2090 81-HD	ABC2040 66-C	ABC2040 66-C	ABC2040 66-C	ABC2070 66-C	ABC2140 66-C	ABC2100 66-C				Certificate	ABC2*P	ABC2*C	ABC2*D	ABC1040
4	ID 3	Degree	ABC2110 50-P	ABC1110 50-P	ABC1110 50-P	ABC1040 61-C								Degree	ABC2*C	ABC2*D
5	ID 4	Diploma	ABC2110 50-P	ABC1110 50-P	ABC1110 50-P	ABC1040 61-C
Sheet1

Cell Formulas
Range	Formula
K2	K2	=SUM(COUNTIF(C2:J2,$O$2:$Q$2))
L2	L2	=SUMPRODUCT(1*(C2:J2=$N$6:$N$8))
Press CTRL+SHIFT+ENTER to enter array formulas.

I can get this to work if I stick with Countif but I'd like to know what I have to tweak to get the Sumproduct to work. Basically in this example I am trying to work out credits based on student results. There is an added complication here in that the number of credits will vary according to the level of qualification so in this system a certificate would receive less credits than a diploma. I think I can work out a lookup/choose option for that part. It's the Sumproduct part that has me puzzled. I have used Sumproduct for a similar example and it worked fine, I get a little closer if I have the credits displayed vertically but I'm still getting #N/A errors so I thought I'd post here. I've only worked out the credits for the first record, once I get clarity for that I'll fill in the rest.

 

[Norie]

Well part of the reason the SUMPRODUCT formula you posted doesn't work could be that the criteria range, N6:N8, is empty.

Also, you can't use wildcards in SUMPRODUCT.

 

[jasonb75]

I get a little closer if I have the credits displayed vertically

Because the range and the criteria both contain multiple cells they need opposite orientation.
Using ={SUMPRODUCT(1*(C2:J2=$O$2:$Q$2))} the comparisons are parallel, C2 is compared to O2, D2 to P2 and E2 to Q2. There is nothing in the second range to compare F2:J2 against which is why you get #N/A.

As @Norie has already said, you can't use wildcards with sumproduct criteria, to do what you are trying (without the wildcards) you would need to use the transpose function on one of the ranges, e.g.
{=SUMPRODUCT(1*(C2:J2=TRANSPOSE($O$2:$Q$2)))}

Although, as with most things there is a workaround

{=SUMPRODUCT(--ISNUMBER(SEARCH(TRANSPOSE($O$2:$Q$2),C2:J2)))}

Personally, I would stick with countif.

 

[Tigerexcel]

Not sure what happened there, formula should be SUMPRODUCT(1*(C2:J2=$O$2:$Q$2)). This returns #N/A rather than 0.

 

[Tigerexcel]

Thanks Norie and Jason for the explanation, I understand the limitations of SUMPRODUCT. The Jason workaround is nice and shows what can be done with SUMPRODUCT but COUNTIF is much easier.
I have read amongst the literature on Excel that SUMPRODUCT could be the single most useful function and so have invested some time in it but am beginning to have some doubts about the claims made about it.

 

[jasonb75]

I have read amongst the literature on Excel that SUMPRODUCT could be the single most useful function

That probably was the case, common use of sumproduct was for counting or summing values in rows which meet multiple criteria. The introduction of sumifS and countifS in excel 2007 provided a more efficient alternative.

For a number of years such sumproduct formulas were still a necessity for people needing to maintain backwards compatibility with colleagues still using older versions of excel. There are still some complex formulas where sumproduct is the preferable method but they are getting fewer as more new functions are being added.

 

[jasonb75]

Having taken a second look at your example, is this something that you can work with?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID no.	Course	Unit1	Unit2	Unit3	Unit4	Unit5	Unit6	Unit7	Unit8	Credits		Level	Criteria	Credits
2	ID 1	Diploma	ABC2020 56-P	ABC2040 28-N	ABC1110 30-N	ABC1090 61-C	ABC2690 61-C	ABC2090 28-N	ABC1110 50-P	ABC1040 61-C	4		Diploma	ABC2*P	2
3	ID 2	Certificate	ABC2020 36-N	ABC2090 81-HD	ABC2040 66-C	ABC2040 66-C	ABC2040 66-C	ABC2070 66-C	ABC2140 66-C	ABC2100 66-C	7		Diploma	ABC2*C	2
4	ID 3	Degree	ABC2110 50-P	ABC1110 50-P	ABC1110 50-P	ABC1040 61-C					0		Diploma	ABC2*D	2
5	ID 4	Diploma	ABC2110 50-P	ABC1110 50-P	ABC1110 50-P	ABC1040 61-C					2		Certificate	ABC2*P	1
6													Certificate	ABC2*C	1
7													Certificate	ABC2*D	1
8													Certificate	ABC1040	1
9													Degree	ABC2*C	3
10													Degree	ABC2*D	3
Sheet1

Cell Formulas
Range	Formula
K2:K5	K2	=SUMPRODUCT(($M$2:$M$10=B2)*ISNUMBER(SEARCH($N$2:$N$10,C2:J2))*$O$2:$O$10)

 

[Tigerexcel]

Thanks again Jason for the clarification regarding SUMPRODUCT, I'm wondering are there any examples where this function must be used because there are no other choices?

Anyway your last post is terrific, talk about inventive and efficient! I like your approach in a one-stop approach. The number of credits in column K are slightly out eg for student ID 1, they should receive 2 credits, ID 3 should receive 1 credit.

 

[CA_Punit]

Well part of the reason the SUMPRODUCT formula you posted doesn't work could be that the criteria range, N6:N8, is empty.

Also, you can't use wildcards in SUMPRODUCT.

Hii i have tied to use * card in sumproduct and it seems it is working fine

Book1
	K	L	M	N	O	P
4
5		abc20PUNITABC	1
6		20abcpunituu	2
7
8
9
10					*PUNIT
11
12			3
13
14
15
16
Sheet8

Cell Formulas
Range	Formula
M12	M12	=SUMPRODUCT(M5:M6,--ISNUMBER(SEARCH(O10,L5:L6)))

Is there any specific situation where it doesn't work. Please guide us for the constraint

 

[jasonb75]

Thanks again Jason for the clarification regarding SUMPRODUCT, I'm wondering are there any examples where this function must be used because there are no other choices?

The method that I used in post 7 needs either sumproduct or sum with ctrl shift enter. The method doesn't work with sumifs as the wildcards would be in the wrong part of the formula.

Then there is the basic use of sumproduct for which it was originally intended, to multiply the values in each row or a range (product) then add the results of each row (sum) in a single formula.

The number of credits in column K are slightly out eg for student ID 1, they should receive 2 credits, ID 3 should receive 1 credit.

Not sure how you're seeing that based on the example, the numbers in column O denote the credits awarded, I just used 1, 2 and 3 for testing. As far as I can see all of the results in the sample match the criteria and the test values, ID1 receives 2 credits each for Unit1 and Unit5, ID3 has no units that match the criteria table.