Thought I was done with this spreadsheet but I noticed that on the cells : K18, L18, M18 & N18 are not adding like I would like them to...
When I select "Maybe" on column F3:F11 it deducts it from the cells indicated above... the only time it should deduct is if we select "YES" if the answer is "NO" or "MAYBE" then the amounts on column "E" should be added... I tried the SUMIF function but didn't work.
Last edited by jgomez; 01-28-2011 at 05:37 PM.
As I said in your other post
Perhaps: =SUMIF(F3:F11,"<>Yes", E3:E11)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
You can use the formula you have but change this: ="no" to: <>"Yes"
You would need SUMIFS() otherwise and it is not available in Excel 2003
NBVC that works but then it excludes the breakdown of the cities past dues...
Thanks cutter, I will have to wait for 2007 version, should get it next week. I changed my formula like you said and it doesn't work like I would like it to.
@jgomez, Cutter's suggestion:
does exactly as requested, eg:Originally Posted by Cutter
the only values that would be excluded for the given city would be where Loan Drop = Yes.
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
In what way does it not work the way you'd like it to.
By making the changes I suggested to each of those formulas in K18:N18 you'll get 4 results that, when summed, equal the result given by the SUMIF() given by NBVC.
sorry, I was looking at the formula in E13
well I tried all of your suggestions and anytime I change it and choose "maybe" it still adds the numbers when it shouldn't. the only time the numbers should change if is "yes" is selected... I'm still messing with it.
Check the attached and play with it.
Is it still not working as expected?
Re: How to exclude 2 choices from being subtracted.
Yes, Thank you so much.
How did you get it to distinguish the city???? I see your formula but I thought you would have to include the name of the city... you didn't..
I just replaced the city name with the cell reference containing the city name
this part:
SEARCH(K17,$G$3:$G$11))
K17 is the cell containing the city McAllen
and I also added the $ signs, so that you copy the formula across the 4 columns and not have to re-type it in each time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks