Identifying NULL results from a SUMIF Function

user_11941672 0
Viewed 65

Consider the following two excel sheets (examples only):

Sheet1:

Name   Marks
-------------
John   95
Alex   89
Kevin  97
John   86
Peter  99
Kevin  100
Alex   91
Peter  20

Sheet2

Name   Total Marks
-------------------
John      181
Kevin     197
Peter     119
Alex      180
Robin      0

Sheet1 is the Base data and Sheet2 is the derived data, where the Total Marks is calculated using SUMIF function. As seen in Sheet2, Robin has a total score of 0, as the entry for Robin is not available in the Sheet1. Is there any way where the Total Score for Robin remains as blank since there is no entry in the main sheet? I want the resultant excel to look like the following:

Name   Total Marks
-------------------
John      181
Kevin     197
Peter     119
Alex      180
Robin
excel
2 Answers

First you check if the Name exists in the Sheet1. If not, output is blank. Else Sumif...:

=IF(ISERROR(MATCH(A2;Sheet1!$A$2:$A$9;0));"";SUMIF(Sheet1!$A$2:$A$9;A2;Sheet1!$B$2:$B$9))
user_120803780

You can use this formula:

=IFERROR(SUMPRODUCT(($A$2:$A$9=D2)*IF(COUNTIF($A$2:$A$9;D2)>0;$B$2:$B$9;""));"")

It will return 0 if a candidate is found with score 0 and a blank if the candidate is not in the list.

This works because we generate an error when calculating SUMPRODUCT with blanks (which is returned by the IF function).

SUMIF / SUMPRODUCT that shows a blank if a criteria is not in the criteria range

user_124188420
Related