If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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... |
Thread Tools | |
Display Modes | |
|
|