Formula Query - Shazza

[Shazaxacpcc]

I need to quickly check a large amount of data for the following:

Look at column B and if there are multiple entries for the same reference
number, look at column F and add the totals for that reference number and if
it equals 0, identify those items with 'nil'

Can anyone help me write a formula that will accomplish this??

Thanks in advance.

Shazza

 

[Sheeloo]

One way is to enter this in Col C (or whichever Col you have available) and
Row 2 (assuming header rows)

=IF(SUMPRODUCT(--(B1:B100=B1),(C1:C100))=0,"Nil",SUMPRODUCT(--(B1:B100=B1),(C1:C100)))

You can then use Filter|Advance Filter|Unique Values to get the unique
values in Col B and their corresponding sum...