Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

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

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25419
  • Total likes: 721
  • DansDeals.com Hat Tips 15
  • 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: 25419
  • Total likes: 721
  • DansDeals.com Hat Tips 15
  • 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: 8388
  • Total likes: 74
  • 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: 7355
  • Total likes: 301
  • 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: 7116
  • Total likes: 140
  • DansDeals.com Hat Tips 4
  • 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: 7116
  • Total likes: 140
  • DansDeals.com Hat Tips 4
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2012
  • Posts: 5409
  • Total likes: 254
  • DansDeals.com Hat Tips 15
    • 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: 3001
  • Total likes: 13
  • 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: 25419
  • Total likes: 721
  • DansDeals.com Hat Tips 15
  • 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

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
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 »

Offline AJK

  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • ********
  • Join Date: Jun 2011
  • Posts: 25419
  • Total likes: 721
  • DansDeals.com Hat Tips 15
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • 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: 7116
  • Total likes: 140
  • DansDeals.com Hat Tips 4
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • 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: 25419
  • Total likes: 721
  • DansDeals.com Hat Tips 15
  • 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: 7355
  • Total likes: 301
  • 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: 7116
  • Total likes: 140
  • DansDeals.com Hat Tips 4
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • 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.