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.

 

[jasonb75]

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

You can't use a wildcard as a direct criteria (in the way that the OP tried to in post 1).
If you read the thread before posting you would see that I already explained the problem and demonstrated the use of ISNUMBER(SEARCH( as a method that does work in post 3.

 

[Tigerexcel]

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.

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.

I can see what I did wrong now (it's the ABC1040), your formula works great for the information I provided and will work for 98% of the data. The problem is when someone records a N for ABC1040, in this case there is no credit and should not record a 1 in column O.

 

[jasonb75]

From that, am I right in saying that ABC1040 61-C should be counted (subject to course type being correct) but not ABC1040 61-N ?

To exclude it, the easiest way would be to list the alternatives in the reference table, ABC1040*S ABC1040*N etc but with a zero credit in column 0 for the latter.

 

[Tigerexcel]

From that, am I right in saying that ABC1040 61-C should be counted (subject to course type being correct) but not ABC1040 61-N ?

To exclude it, the easiest way would be to list the alternatives in the reference table, ABC1040*S ABC1040*N etc but with a zero credit in column 0 for the latter.

Thanks, increasing the size of the reference table did the trick. Impressive use of Sumproduct, you've got no idea how much time this will save, thanks again.