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 Problem  (Read 32280 times)

Offline skyguy918

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2311
  • Total likes: 11
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #30 on: January 07, 2014, 09:36:12 AM »
Pres Alt+F11 to open the VBA editor. Right-click where it says VBAProject([name of your file]), Insert, Module. Then paste this code in there:
Quote
Sub SumCost()

Total = 0

For Each c In Range("B1:E10").Cells
    If c = "Cost" Then
        Total = Total + c.Offset(0, 1).Value
    End If
Next

Range("A1").Value = Total

End Sub
Just replace the the 2 instances of "Range" with the appropriate values for your file. The first should be the range you want to search and the second is where you want excel to paste the sum.

You can set this macro to a hotkey so that you can just click it once on each sheet easily. Back in Excel, click View, Macros, View Macros, select the macro you just created (SumCost), click options, and add a letter in the box after "Ctrl+".

Remember that as written the macro performs the code on the active sheet.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3159
  • Total likes: 45
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #31 on: January 07, 2014, 12:13:55 PM »
Pres Alt+F11 to open the VBA editor. Right-click where it says VBAProject([name of your file]), Insert, Module. Then paste this code in there:Just replace the the 2 instances of "Range" with the appropriate values for your file. The first should be the range you want to search and the second is where you want excel to paste the sum.

You can set this macro to a hotkey so that you can just click it once on each sheet easily. Back in Excel, click View, Macros, View Macros, select the macro you just created (SumCost), click options, and add a letter in the box after "Ctrl+".

Remember that as written the macro performs the code on the active sheet.
This looks like you're saving the macro to the Active workbook.
Isn't there a way to save it to the Excel Personal workbook, so the shortcut Ctrl+* is available anytime after?

Edit: Instructions here.
« Last Edit: January 07, 2014, 12:17:28 PM by lubaby »
Once the game is over, the king and the pawn go back in the same box.

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 23986
  • Total likes: 33
  • DansDeals.com Hat Tips 10
  • 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 #32 on: January 07, 2014, 12:15:01 PM »
Pres Alt+F11 to open the VBA editor. Right-click where it says VBAProject([name of your file]), Insert, Module. Then paste this code in there:Just replace the the 2 instances of "Range" with the appropriate values for your file. The first should be the range you want to search and the second is where you want excel to paste the sum.

You can set this macro to a hotkey so that you can just click it once on each sheet easily. Back in Excel, click View, Macros, View Macros, select the macro you just created (SumCost), click options, and add a letter in the box after "Ctrl+".

Remember that as written the macro performs the code on the active sheet.

Thank you very much.

Even the thought of VBA scares me... I'll do it if I must, but is there no way to do this with formulas?
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 skyguy918

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2311
  • Total likes: 11
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #33 on: January 07, 2014, 12:28:19 PM »
This looks like you're saving the macro to the Active workbook.
Isn't there a way to save it to the Excel Personal workbook, so the shortcut Ctrl+* is available anytime after?

Edit: Instructions here.
Can't imagine why you'd want to use this particular macro in multiple workbooks, but if you did, your link is a great resource.
Thank you very much.

Even the thought of VBA scares me... I'll do it if I must, but is there no way to do this with formulas?
There is, with array enabled formulas. Not sure which is technically better or more efficient, but you can try this:
Quote
=SUM(IF(range="count",OFFSET(range,0,1),0))
Copy the text into a cell, replace the italicized range with the range you want to look in, and instead of pressing enter to save the formula into the cell, press ctrl+shft+enter. This turns it into an array enabled formula.

Offline noturbizniss

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2012
  • Posts: 5885
  • Total likes: 10
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Location: North Jersey
Re: Excel Problem
« Reply #34 on: January 07, 2014, 12:29:11 PM »
Thank you very much.

Even the thought of VBA scares me... I'll do it if I must, but is there no way to do this with formulas?
not one formula in one cell. The sheet is kind of messy and excel works best with neatness.
READ THE DARN WIKI!!!!

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

Offline skyguy918

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2311
  • Total likes: 11
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #35 on: January 07, 2014, 12:36:01 PM »
not one formula in one cell. The sheet is kind of messy and excel works best with neatness.
I already tested the formula I posted right above you in a simplified version of AJK's sheet, and it works - in one cell.

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 23986
  • Total likes: 33
  • DansDeals.com Hat Tips 10
  • 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 #36 on: January 07, 2014, 12:45:34 PM »
What'd I do wrong?

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 churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6209
  • Total likes: 34
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #37 on: January 07, 2014, 12:49:51 PM »
change "count" to cost  :)

Offline noturbizniss

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2012
  • Posts: 5885
  • Total likes: 10
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Location: North Jersey
Re: Excel Problem
« Reply #38 on: January 07, 2014, 12:50:56 PM »
I already tested the formula I posted right above you in a simplified version of AJK's sheet, and it works - in one cell.
Yup, totally forgot about array formulas.
READ THE DARN WIKI!!!!

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

Offline skyguy918

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2311
  • Total likes: 11
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #39 on: January 07, 2014, 12:51:56 PM »
change "count" to cost  :)
Yup, sorry about that.

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 23986
  • Total likes: 33
  • DansDeals.com Hat Tips 10
  • 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 #40 on: January 07, 2014, 01:00:12 PM »
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 churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6209
  • Total likes: 34
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #41 on: January 07, 2014, 01:07:24 PM »

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 23986
  • Total likes: 33
  • DansDeals.com Hat Tips 10
  • 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 #42 on: January 07, 2014, 01:08:14 PM »
Slowly but surely?

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 churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6209
  • Total likes: 34
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #43 on: January 07, 2014, 01:08:59 PM »
Slowly but surely?


forgot the ctr shift

Offline yitzf

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2423
  • Total likes: 3
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #44 on: January 07, 2014, 01:09:23 PM »

Is excel case sensitive?
The word "cost" in your formula is lower case.