• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct Question

J

Jai9

New Member
Hi All,


A small sumproduct question.


Can sumproduct be used to look up text data?


I have a fictitious data with names in column A, subject 1, subject 2 etc in column B onwards and Grades in column H.( Grades are A, B etc.)


Using sumproduct I can find the marks obtained by a person in any subject. ( Marks are numbers ).


When I use the same logic to find grade, I get #Value! error.


I dont seem to find the reason for this when I do a google search.


Any thoughts?


Jai
 
Hi,


=SUMPRODUCT((A2:A35=A21)*(B1:G1=E1)*(B2:G35))


gives correct answer for the number of marks.


=SUMPRODUCT((A2:A35=A21)*(B1:H1=E1)*(B2:H35))


gives an error.


Jai
 
@Jai9


Hi


Sorry it is little bit confuse to me can you give some more details about your columns

1. ColumnA2:A35 = Subjects

2. Columnb1:g1 = Student Names

3. ColumnH:H = Grade


is This Correct


Thanks


SP
 
Hi Jai9 & sgmpatnaik,


You are getting Value Error because Col H is text, it can not be summed using SUMPRODUCT(). You can simply use:


Code: 
=VLOOKUP(A20,$A$2:$H$35,8,TRUE)


to find the grade of student in A20, Col H contains the Grade.


Regards,
 
Hi Faseeh,


You mean to say since the grades are texts, they cant be added using sumproduct and I am getting #Value! error.


So sumproduct works only for numbers.


Right?


Jai
 
Yes Jai9,


You can validate criteria that are text using "=" sign just like you did for name and Subject, but for adding up something, it need to be a number. When you validate a Text this way, it returns a series of True & False, when this series of True/False is multiplied with Number, False returns zeros and true give corresponding values, thus SUMPRODUCT sums these zeros and non-zeros, but with text, you can not actually perform multiplication of True/False with Text that give you a value error.


Hope that helps,


Regards,
 
You must log in or register to reply here.
Back
Top