Using COUNTIF for 2 dats values

Microsoft Excel 2007
September 9, 2009 at 04:36:32
Specs: Windows XP
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

See More: Using COUNTIF for 2 dats values

Report •

September 9, 2009 at 05:10:05
Review the Excel Help files for SUMPRODUCT.

=SUMPRODUCT(('ADPO Stock'!N2:N26="Storage RTM")*('ADPO Stock'!L2:L26>0))

Report •

September 9, 2009 at 07:32:31
Excellent as always, thank you very much.

Report •

September 9, 2009 at 08:04:12
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-In

1. 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 show

3 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 menu

7 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 Finish

The 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

Report •

Related Solutions

September 9, 2009 at 08:13:02
Oops - You are using 2007
Conditional sum is under Formulas

Report •

September 9, 2009 at 08:23:18
SUMPRODUCT from DerbyDad03 is much nicer!

I must remember that one.

Report •

Ask Question