In Excel 2007, I have a bunch of columns with one number in each column. Some positive, some negative. How do i count the amount of positives and subtract the count of only the negative numbers that are equal to the positive numbers (ex. there is a 2 , and there is a -2.)?? Thanks.
Use the Countif Functon..... example =COUNTIF(A1:A6,"< 0") will count all negative numbers then you can use =COUNTIF(A1:A6,">0") for all positive numbers..using these together in your formula will yield the results
=SUM(COUNTIF(A1:A6,">0")-COUNTIF(A1:A6,"< 0"))
Last edited by optomyst; 11-03-2011 at 03:36 PM.
I use =countif(range,">0") - countif(range,"<0")...however that automatically counts, for example, a purchase of 1 and a return of 0.5 as a 0. I need a formula that will still keep that as a 1. FYI, this is for a purchases and returns report. So if a customer buys 1 case and return half the next day, its still considered a purchase (1). Any ideas? Someone showed me a formula that started off {=sum(if......and I cant remember the rest bc it was lost. Thanks.
Ky,
... a sale is a positive number.. and a return is a negative number? Also, any partial case, needs to be rounded to the next whole number? for exam. .5 = 1 and -.5 = -1?
Yes. Positive and negative. However, NO ROUNDING. As long as a return (ex. returned half a case (-0.5)) doesnt negate a purchase or sale (ex. bought one case (1)), there is still a sale. My formula says that in this example, there was no sale, bc it subtracts the amount of negative numbers from the amount of positive numbers. It doesnt consider the actual value of the cells. Any ideas?
Can you send me an example ..and the correct answer you are looking for?
OK attached is a file demonstrating what the answer is with my formula and what the answer should be. Thanks for the help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks