Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Query - Shazza
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Query - Shazza
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... "Shazaxacpcc" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Query | Excel Worksheet Functions | |||
formula Query | Excel Discussion (Misc queries) | |||
Formula query | Excel Worksheet Functions | |||
formula query | Excel Discussion (Misc queries) | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |