Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 42947 times)

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6516
  • Total likes: 77
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #15 on: December 11, 2013, 05:44:38 PM »
If the were hidden they wouldn't "partially" disappear.

Offline alpicone

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Aug 2012
  • Posts: 873
  • Total likes: 0
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #16 on: December 11, 2013, 07:12:02 PM »
If the were hidden they wouldn't "partially" disappear.

Row height then?

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6516
  • Total likes: 77
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #17 on: December 11, 2013, 07:35:43 PM »

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24332
  • Total likes: 67
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #18 on: January 07, 2014, 02:08:25 AM »
Excel Experts:

What would the formula be if I wanted to search a particular sheet for the word "Cost" and then sum all the values in the cell immediately adjacent that "Cost" cell?

2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline lechaim2life

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jul 2011
  • Posts: 151
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24332
  • Total likes: 67
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #20 on: January 07, 2014, 02:24:32 AM »
Unfortunately, SUMIF alone will not work for me, as my "criterion" is not in a single column.

Simplified:



I'd need like four different formulas if I wanted to only sum those cells to the right of "Cost."

e.g. -- =SUMIF(A:A,"cost",B:B)+SUMIF(B:B,"cost",C:C)+SUMIF(C:C,"cost",D:D)+SUMIF(D:D,"cost",E:E)

But given the size of my spreadsheet, that's not going to work.

Seems I need a formula which will search for "Cost" everywhere, and then sum only the cell to the right of that cell.

Any other ideas?
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline lechaim2life

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jul 2011
  • Posts: 151
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #21 on: January 07, 2014, 02:41:44 AM »
Would you be able to play around with the formatting to make it more organized or is it too much data?

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24332
  • Total likes: 67
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #22 on: January 07, 2014, 02:49:22 AM »
Too much data :(
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper

Offline Drago

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 8118
  • Total likes: 3
  • DansDeals.com Hat Tips 18
    • View Profile
Re: Excel Problem
« Reply #23 on: January 07, 2014, 03:29:00 AM »
This is a messy approach, but you can create a new tab, and create a forumla in each cell to reference your original tab that if A1 (etc) = cost, then give value of cell to the right of it.
Then you'll have a tab full of the amounts only, and you can sum them all up.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6516
  • Total likes: 77
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #24 on: January 07, 2014, 08:22:32 AM »
Unfortunately, SUMIF alone will not work for me, as my "criterion" is not in a single column.

Simplified:



I'd need like four different formulas if I wanted to only sum those cells to the right of "Cost."

e.g. -- =SUMIF(A:A,"cost",B:B)+SUMIF(B:B,"cost",C:C)+SUMIF(C:C,"cost",D:D)+SUMIF(D:D,"cost",E:E)

But given the size of my spreadsheet, that's not going to work.

Seems I need a formula which will search for "Cost" everywhere, and then sum only the cell to the right of that cell.

Any other ideas?
Might want to consider using the "match" "index" and sum formulas. Sorry cant think it through now. Good luck

Offline noturbizniss

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2012
  • Posts: 6167
  • Total likes: 31
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Location: North Jersey
Re: Excel Problem
« Reply #25 on: January 07, 2014, 08:39:08 AM »
If columns a, c, e, etc say cost and b, D, D, etc have values than you can insert row on top and do sum if on each pair of columns then sum row 1. The alternative would be some kind of vba
READ THE DARN WIKI!!!!

Chuck Norris...
...can still do FT method
...READS THE WIKI!!!

Offline noturbizniss

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2012
  • Posts: 6167
  • Total likes: 31
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Location: North Jersey
Re: Re: Excel Problem
« Reply #26 on: January 07, 2014, 08:52:32 AM »
If columns a, c, e, etc say cost and b, D, D, etc have values than you can insert row on top and do sum if on each pair of columns then sum row 1. The alternative would be some kind of vba
Just looked at image again you can do that on top of every column
READ THE DARN WIKI!!!!

Chuck Norris...
...can still do FT method
...READS THE WIKI!!!

Offline EJB

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 3885
  • Total likes: 36
  • DansDeals.com Hat Tips 13
    • View Profile
Re: Excel Problem
« Reply #27 on: January 07, 2014, 09:06:20 AM »
VBA is the way to go if you want anything to be done efficiently. You could make a sluggish array summing all cells to the right of  a cell that matches "Cost" (if that's always the case).

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 2945
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #28 on: January 07, 2014, 09:07:14 AM »
VBA is the way to go if you want anything to be done efficiently. You could make a sluggish array summing all cells to the right of  a cell that matches "Cost" (if that's always the case).
Depending on the size of the data, it might not be so sluggish.

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 24332
  • Total likes: 67
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Programs: United Concierge Key; Delta Global Services; American Chairman; US Airways 1K; Hilton Sapphire; Hyatt Tritium; Marriott Californium; Starwood Kryptonium; Hertz Plutonium; National Adamantium, Avis Executive Proactanium
Re: Excel Problem
« Reply #29 on: January 07, 2014, 09:28:37 AM »
What would the array look like?

=sum(if(

?
2015: 116K bkd | 1.6M brnd | F: OZ,NH,AA,EK | J: UA,CA,TK,DL,TN,AF,VA | LIH,NRT,ROR,PEK,CNS,BOB,MEL,TLV & Pacific Hopper