Hi guys fairly new to the whole excel formulas outside of basic summing.
What I'm trying to do is taking values from Colomn C and adding them up based on if the info in Colomn D is Yes or No
I'm stumped, I've tried a couple different things but they dont work out. If someone could give me a hand and getting a code to work I would very much appreciate it.
Also if possible give some tips on what would be needed to setup a drop down box to modify and show only those sums based on the yes or no.
Thanks
Last edited by littleman; 08-11-2010 at 01:20 PM.
In C19, try this:
=SUMIF(D5:D18, "<>Y", C5:C18)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks for the quick reply, I tried that out and it works with what I'm basically looking to do slight mod to use either Y or N.
Now to work on a drop box to easily access this info
I didn't use the Y/N thing because in my opinion, the only value that is a concern is "Y". If the value is Y then the ticket is canceled. Any other value means "no", whether its "N" or blank or "cat"...only "Y" means canceled.
Your data layout does not lend itself to simple AutoFilter drop down filtering. You only get one per sheet.
Your best bet may be Excel's LIST feature. Highlight one set of data and press CTRL-L, click on [x] My list has headers...and OK.
Repeat for each of our sets and you now have drop downs you can use.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Change the formula in C18 to:
=SUBTOTAL(109, C4:C17)
Now use the drop down in column D created when you turned on the LIST function to filter for just Y and the subtotal will display a total only for the visible rows.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
just tried your new formula and have to say that works out better and is fairly simple to change over.
Again thanks so much saved me time and headache
SUBTOTAL() has 22 different functions, press F1 and read up on them all, very useful function.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks