Hello everyone, I have a spreadsheet in which I want to sum numbers from Column F, based on the responses from three other columns (C, D, and E). The three columns represent the answer as to whether or not an invoice is present in a source location, and there are three locations, thus three columns.
The only responses that will be put in these columns is Y, N, or I (Yes no or incomplete). Regardless of whether an invoice is found or not, if the invoice value is known, that value will be put in column F.
What I'm trying to accomplish is to have three separate SUM columns, one for each possible response, Y, N, or I, but I also do not want duplicate entries. It is likely that the responses in these columns will contradict one another. A document may be found in one location, but not the other two, or it may be found in 2 our of 3, or even all three locations.
This is what I'm after.
If the answer is Y in any or all of the three columns, I want the value in column F, related to that specific answer, added, only once, to the SUM column for Y, and to ignore any N, I or duplicate Y responses in the remaining columns.
If there are no Y responses, and answer is I, in any column, I want that value added only to the I SUM column, ignoring any duplicate I responses or N responses.
If there are no Y or I responses, I want only one N response added to the N Sum column, ignoring any duplicate N responses.
Is this doable?