Sumproduct question

  • Thread starter Thread starter Donna@Dell
  • Start date Start date
D

Donna@Dell

Hello -

I need a formula that will count the number of lines that are greater than 0
in one column but also need to subtract the number of lines that are greater
than 0 in another column.

There are 16 lines that are greater than 0, within that subset there is 1
line item that is greater than 0 in the other column. I've tried sum(if....,
sumproduct, countif doesn't work.

HELP!

Donna
 
Hi,

If you want to count the number of rows where column A is greater than 0 but
column B is not greater than 0 (i.e., less than or equal to 0), use the
following formula. Change the data ranges in the formula (i.e., A1:A100 and
B1:B100) to suit to your data.

=SUMPRODUCT((A1:A100>0)*(B1:B100<=0))

If this helps, please click "Yes".

Regards,
B. R. Ramachandran
 
Here is a sample:
A B C
150 150 0
20 20 0
600 600 0
0 75 75
0 100 100
9 9 0
0 14 14
0 10 10
80 80 0
12 12 0
0 34 0
85 85 0
0 200 0
0 250 0
40 40 0
25 25 0
500 500 0
455 455 0
0 100 100
75 75 0
0 5 5
20 20 0
0 25 25
110 110 0
100 100 0
200 225 25
190 190 0
0 100 100
210 210 0
50 50 0
183 183 0
35 35 0
40 40 0
0 100 0
0 200 0
138 138 0
0 126 0
0 75 75
0 50 50
380 380 0
200 200 0

I want to count Column C if >0 then of the subset of where Column C is >0 i
want to subtract Column B where >0.

Here is the subset:
A B C
0 75 75
0 100 100
0 14 14
0 10 10
0 100 100
0 5 5
0 25 25
200 225 25
0 100 100
0 75 75
0 50 50

The result I am looking for is 10

Possible?
 
Back
Top