Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct question
Just use a countif on the first - a countif on the second -- Don Guillett Microsoft MVP Excel SalesAid Software "Donna@Dell" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct question | Excel Worksheet Functions | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
another sumproduct question | Excel Worksheet Functions | |||
Sumproduct question | Excel Worksheet Functions | |||
sumproduct question | Excel Worksheet Functions |