ExcelBanter - Sumproduct question

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Sumproduct question (https://www.excelbanter.com/excel-discussion-misc-queries/251681-sumproduct-question.html)

Sumproduct question

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

Sumproduct question

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:A1000)*(B1:B100<=0))

If this helps, please click "Yes".

Regards,
B. R. Ramachandran

"Donna@Dell" wrote:

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

Sumproduct question

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?

"B. R.Ramachandran" wrote:

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:A1000)*(B1:B100<=0))

If this helps, please click "Yes".

Regards,
B. R. Ramachandran

"Donna@Dell" wrote:

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

Sumproduct question

-Disregard my last question... this worked!!!

Thank you very much!

-D

"B. R.Ramachandran" wrote:

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:A1000)*(B1:B100<=0))

If this helps, please click "Yes".

Regards,
B. R. Ramachandran

"Donna@Dell" wrote:

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