Hi there Does anyone know how I can count the number of times "Storage RTM" appears in the range 'ADPO Stock'!N2:N500 ONLY if the adjacent cell ('ADPO Stock'!L2:L500) is > 0 ????

I tried the formula below, but it counts the number of times 'ADPO Stock'!L2:L500 is > 0 regardless of what is in 'ADPO Stock'!N2:500=AND(COUNTIF('ADPO Stock'!N2:N500,"Storage RTM"))*(COUNTIF('ADPO Stock'!L2:L500,">0"))

Many thanks

Cheryl

Review the Excel Help files for SUMPRODUCT. =SUMPRODUCT(('ADPO Stock'!N2:N26="Storage RTM")*('ADPO Stock'!L2:L26>0))

Excellent as always, thank you very much.

The Conditional Sum function will do this for you.

As it's not loaded in Excel by default, you will need to add the Add-In1. In Tools-Add-Ins make sure that Analysis Toolpack- VBA is checked.

2 Now, under the Tools Menu make sure that 'ConditionalSum...' is in the list. If you checked Analysis Toolpack- VBA, you may have to restart Excel for it to show3 Next to your table of quantities and Information such as 'Storage RTM', add a third column with the value '1' in every cell. (I know this sounds odd but it is necessary for this to work!)

4. Make sure each of you three columns has a title in the cell above the first data entry. The column of 1's can be labeled 'Counter'5 Select your whole table including the titles

6 Select ConditionalSum... from the Tools menu7 A dialog box opens with your selected range already showing, click Next

8 The next box says Column to Sum - select Counter if it's not already been selected (This is the column of 1's which will be summed when the criteria are matched)9 In the three boxes select 'Quantity, '>", and '0', click Add condition

10 Repeat for the column containing description and enter "=", and 'Storage RTM', click Add condition

11 Click Next and select 'Copy just the formula to a single cell', then click Next

12 Enter or select the cell to show the result, and click FinishThe number of entries with Storage RTM and Values >0 should show in the result cell

If you look at the result cell you will see that the formula is surrounded by curly braces {}. If you ever click on the formula or try to edit it, to make it work you have to use Ctrl+Shift+Enter, if not you don't get the curly braces and the formula will not work.

(Ctrl+Shift+Enter only works when the cursor is in the formula bar)

Once you have used the conditional sum wizard, you can replace fixed values such as '0' or 'Storage RTM' with cell references, but remember after doing this to use Ctrl+Shift+Enter

By using references to other cells you can change the criteria easily, selecting values other than zero, or other text.

The Conditional Sum function will work with more than just two criteria in additional columns if necessary.

I did a test on a small amount of data in three columns (C, D and E) and 9 rows of data plus the heading. The Counter values of '1' were in column E, and this is what the formula looked like:

{=SUM(IF($C$4:$C$12=0,IF($D$4:$D$12="Storage RTM",$E$4:$E$12,0),0))}Hope this works for you

PS You can write these formulas manually, finishing with Ctrl+Shift+Enter which adds the Curly Braces and makes the whole thing work - but the wizard is a good place to start

Oops - You are using 2007

Conditional sum is under Formulas

SUMPRODUCT from DerbyDad03 is much nicer! I must remember that one.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History