Forum Discussion
Solved
Summing an area with one criteria on multiple rows
I have a spreadsheet and trying to sum all of the rows and columns that match the criteria in the left column. I am looking for the sum of Columns F, G, H, I, J that match "1". What function can he...
Thank you so much! I have to admit I don't understand the equation. What is the purpose of the two hyphens?
(A2:A16=A19) returns a series of TRUE/FALSE values: TRUE for each cell in A2:A16 that equals A19, FALSE for all other cells in A2:A16.
The - in -(A2:A16=A19) is a minus sign. It forces Excel to convert the result to numbers: TRUE is treated as 1 and FALSE as 0, so you now have a series of -1 / 0 values.
The second minus sign converts the -1 values to +1. So --(A2:A16=A19) is an array of 1 / 0 values and those can be used in MMULT.
Thank you for this explanation. I realized that the equation is returning a #value! in my spreadsheet. The only thing I can think is happening is that my spreadsheet totals are equations not actual keyed values. Is there a way to sum up the totals in the columns?
