I have a spreadsheet with multiple columns of data, one column is a reference
number, another is a value. I want to write a formula that first looks at the
reference number and if there is more than one entry for the same reference
number, I want it to look at the values and if the sum of the values is nil,
I want it to identify all of the entries for that reference number. eg cells
b3 & b4 have same reference number and the sum of g3 & g4 is nil, I want an
indicator in cell L3 & L4.

Thanks in advance

Put this in L3 (press CTRL-SHIFT-ENTER together instead of ENTER)

=IF(SUMPRODUCT(--(B1:B92=B3),--(G1:G92))=0,"ZERO SUM","Non ZERO")

and copy down

"SUMSUE" wrote:

I have a spreadsheet with multiple columns of data, one column is a reference
number, another is a value. I want to write a formula that first looks at the
reference number and if there is more than one entry for the same reference
number, I want it to look at the values and if the sum of the values is nil,
I want it to identify all of the entries for that reference number. eg cells
b3 & b4 have same reference number and the sum of g3 & g4 is nil, I want an
indicator in cell L3 & L4.

Thanks in advance