Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct counting negative numbers
I have 2 columns as follows:
A B -$24.00 n/a $200 n/a $350 n/a $65 9.30% $32 n/a n/a 5% i need a function that will count the number of times column A appears with n/a only ( so in this case its 4). I have this so far...i don't know how to write it so its counts the negative value: =SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a")) I also need a formula that will count the occurrance of when column B has a value when column A has an n/a. In this case the answer would be 1. I hope that make sense... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct counting negative numbers
Correction:
i need a function that will count the number of times column A appears with n/a in COLUMN B ( so in this case its 4). I have this so far...i don't know how to write it so its counts the negative value: =SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a")) "excelllllllll" wrote: I have 2 columns as follows: A B -$24.00 n/a $200 n/a $350 n/a $65 9.30% $32 n/a n/a 5% i need a function that will count the number of times column A appears with n/a only ( so in this case its 4). I have this so far...i don't know how to write it so its counts the negative value: =SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a")) I also need a formula that will count the occurrance of when column B has a value when column A has an n/a. In this case the answer would be 1. I hope that make sense... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct counting negative numbers
Assuming that in in column A, there is either a number or n/a try this
formula =SUMPRODUCT(--(B1:B100="n/a"),--(A1:A100<"n/a")) this works if B1 - B100 is n/a and so long A1 - A100 is not n/a, so if there is n/a in column B and nothing in column A, it will still count. For the second one, it is the reverse, =SUMPRODUCT(--(A1:A100="n/a"),--(B1:B100<"n/a")) Of course, if in column B, the value is 0%, it will still count. If you do not want to count 0% if there is any, you will need to modify the formula. "excelllllllll" wrote: Correction: i need a function that will count the number of times column A appears with n/a in COLUMN B ( so in this case its 4). I have this so far...i don't know how to write it so its counts the negative value: =SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a")) "excelllllllll" wrote: I have 2 columns as follows: A B -$24.00 n/a $200 n/a $350 n/a $65 9.30% $32 n/a n/a 5% i need a function that will count the number of times column A appears with n/a only ( so in this case its 4). I have this so far...i don't know how to write it so its counts the negative value: =SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a")) I also need a formula that will count the occurrance of when column B has a value when column A has an n/a. In this case the answer would be 1. I hope that make sense... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
change 2000 cells (negative numbers) into positive numbers | Excel Worksheet Functions | |||
counting a string of negative or positive numbers | Excel Discussion (Misc queries) | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions |