SUM corresponding Values for Names which appear only Once
SUM corresponding Values for Names which appear only Once
Column G formulas return the SUM of values – (i) for Unique Names, or (ii) for Names which appear more than once, or (iii) for names which appear only once.
Cell G14 formula: SUM corresponding Values for Names which appear only Once.
———————————————————————————————–
Cell G14 formula returns the SUM of corresponding Values for Names which appear only Once. Names in column A appear either once (Unique) or multiple times – consider only the names which appear once, & return the SUM of corresponding column B values.
Sum Values which Appear Once
Cell G14 formula:
=SUMPRODUCT(( COUNTIF( A2:A20, A2:A20)=1)*( B2:B20))
Explanation – Cell G14 formula (Non-Array Formula):
1) COUNTIF(A2:A20, A2:A20): returns an array of numbers and zeros – {3;3; 2;2; 1;1; 1;2; 1;2; 3;1;0; 0;0;0; 0;0;0} – the numbers indicate the number of times each column A value appears in the column. For each value / cell there will be a number, and if a value occurs multiple times then the same number will appear for each occurrence. For example, the 1st number 3 indicates that cell A2 value appears 3 times, the 2nd number 3 indicates that cell A3 value appears 3 times, the 3rd number 2 indicates that cell A4 value appears 2 times, and so on.
2) (COUNTIF( A2:A20, A2:A20)=1)*( B2:B20): comparing COUNTIF with “=1” will return TRUE for each column A value which occurs only once (ie. the number in point 1) above is equal to 1). And each time these names appear in column A, TRUE will be multiplied with the corresponding column B value. Note that TRUE returns 1 when used for multiplication. This essentially returns the column B value for each occurrence of a value in column A but only those names in column A which appear only once are considered. This formula returns an array of values from column B corresponding to each occurrence of column A value which occurs only once in the column, for ex cells A6, A7, A8, A10, and so on – {0;0;0; 0;20; 788; 900;0; 355;0; 0;250; 0;0;0; 0;0; 0;0}. Using SUMPRODUCT returns the total of all these column B values in cell G14.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell G4 Formula: SUM Unique List of Values only – for multiple occurrences of a value consider ONLY the first occurrence. Names in column A appear either once (Unique) or multiple times – SUM values in column B corresponding to ONLY the first occurrence of a name in column A. Return SUM (from column B) for a Unique List of names in column A, consider only the first occurrence in column A and ignore subsequent occurrences. Array formula (Ctrl+ Shift+ Enter).
Cell G9 Formula: SUM corresponding Values (column B) for only those Names which appear More than Once (column A) – consider corresponding value for each occurrence of these Names. Names in column A appear either once (Unique) or multiple times – consider only the names which appear multiple times. Consider only those column A names which appear multiple times, and return the SUM of corresponding column B value every time these names appear. Non-array formula.