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 277638 times)

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 6521
  • Total likes: 2480
  • DansDeals.com Hat Tips 59
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1580 on: July 04, 2023, 10:35:19 PM »
Looking for the sum of all the (c-d) that have an xy or z in the row, not 1 particular row
You can have a formula on each line in column E and then sum the total

Or chat gpt says
=SUMIFS(C:C, A:A, "x", B:B, "<>") - SUMIFS(D:D, A:A, "x", B:B, "<>")
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17256
  • Total likes: 14050
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1581 on: July 05, 2023, 12:14:55 AM »
That should be a standard SUMIFS.
True
Feelings don't care about your facts

Offline avadah

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2012
  • Posts: 2494
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1582 on: July 05, 2023, 04:16:21 PM »
I'm not understanding.
I want to know the sum of the price column of all the apples and oranges. The sum should be 10 bec i don't want to double count like on row 7.




Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12811
  • Total likes: 3253
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1583 on: July 05, 2023, 04:39:12 PM »
I think you can do this with DSUM



H2 and I3 contain
Code: [Select]
'=apple
L1 contains
Code: [Select]
=DSUM(A:C,3,H1:I3)
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12811
  • Total likes: 3253
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1584 on: July 05, 2023, 04:42:36 PM »


Code: [Select]
=DSUM(A:C,3,H1:I5)
Basically, with DSUM, conditions on the same row are combined with AND, and separate rows are combined with OR.
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3794
  • Total likes: 820
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1585 on: July 05, 2023, 05:24:04 PM »
I'm not understanding.
I want to know the sum of the price column of all the apples and oranges. The sum should be 10 bec i don't want to double count like on row 7.
There are more complicated ways to do this more succinctly, but if you want to understand what you're doing, here's a really simple way to do it. In cell D2, put the formula:
Code: [Select]
=IF(OR(A2="apple",B2="apple",A2="orange",B2="orange"),C2,0)Then copy that formula down coulmn D to the last row. In D1, put:
Code: [Select]
=SUM(D2:D100)(replace D100 with whatever the last row number is).


ETA: Here's an example of a condensed version. It's an array formula, so once you paste the text into a cell, you'd need to press CTRL+SHIFT+ENTER instead of just ENTER.
Code: [Select]
=SUM(IF((($A:$A="apple")*1+($A:$A="orange")*1+($B:$B="apple")*1+($B:$B="orange")*1)>0,$C:$C,0))
« Last Edit: July 05, 2023, 05:30:53 PM by skyguy918 »

Offline avadah

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2012
  • Posts: 2494
  • Total likes: 38
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Jerusalem
Re: Excel Help and Problems
« Reply #1586 on: July 05, 2023, 06:35:19 PM »

ETA: Here's an example of a condensed version. It's an array formula, so once you paste the text into a cell, you'd need to press CTRL+SHIFT+ENTER instead of just ENTER.
Code: [Select]
=SUM(IF((($A:$A="apple")*1+($A:$A="orange")*1+($B:$B="apple")*1+($B:$B="orange")*1)>0,$C:$C,0))
Wouldn't this double count row 7?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3794
  • Total likes: 820
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1587 on: July 05, 2023, 06:43:02 PM »
Wouldn't this double count row 7?
First comes up with a 'sum' in each row that represents the number of times either apple or orange appears in either A or B. But then is checks if that sum is greater than 0- if it is, take the value in C, if not, 0. Then it just sums the rows.

Try it out. You'll see the result is 10 in your example.

ETA: And btw, the *1 essentially converts TRUE to a 1 and FALSE to a 0.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2160
  • Total likes: 363
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1588 on: July 06, 2023, 10:20:49 AM »
ETA: Here's an example of a condensed version. It's an array formula, so once you paste the text into a cell, you'd need to press CTRL+SHIFT+ENTER instead of just ENTER.
Code: [Select]
=SUM(IF((($A:$A="apple")*1+($A:$A="orange")*1+($B:$B="apple")*1+($B:$B="orange")*1)>0,$C:$C,0))
Wouldn't the much simpler this do the same? Seems to be working as expected for me:
Code: [Select]
=SUM(IF((A:A="apple")+(A:A="orange")+(B:B="apple")+(B:B="orange"), C:C, 0))
BTW, CSE for array formulas aren't needed anymore with newer Excel versions that use Dynamic Array Formulas.

Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 524
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1589 on: July 06, 2023, 10:23:30 AM »
Not sure if this is the right place to ask... I'm looking for a program where I can put in charges and payments on one column, each charge/payment one line under the other, and on the column to the right will show the updated calculation next to each line. would appreciate anyone's help. I hope I was clear.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2160
  • Total likes: 363
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1590 on: July 06, 2023, 10:32:42 AM »
Not sure if this is the right place to ask... I'm looking for a program where I can put in charges and payments on one column, each charge/payment one line under the other, and on the column to the right will show the updated calculation next to each line. would appreciate anyone's help. I hope I was clear.
You simply want a running balance which reflects the total after the transaction of this row?

In cell B2 enter formula (to simply copy the first number of the Charges/Payments:
Code: [Select]
=A2Then in B3 use this formula to continue the running totals for all rows:
Code: [Select]
=IF(A3="","",SUM(B2,A3))


Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3794
  • Total likes: 820
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1591 on: July 06, 2023, 10:49:03 AM »
Wouldn't the much simpler this do the same? Seems to be working as expected for me:
Code: [Select]
=SUM(IF((A:A="apple")+(A:A="orange")+(B:B="apple")+(B:B="orange"), C:C, 0))
BTW, CSE for array formulas aren't needed anymore with newer Excel versions that use Dynamic Array Formulas.
I guess so. TIL that a number on it's own with no logical operators is still evaluated as TRUE by the logical test in the IF function.

Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 524
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1592 on: July 06, 2023, 11:10:11 AM »
You simply want a running balance which reflects the total after the transaction of this row?

In cell B2 enter formula (to simply copy the first number of the Charges/Payments:
Code: [Select]
=A2Then in B3 use this formula to continue the running totals for all rows:
Code: [Select]
=IF(A3="","",SUM(B2,A3))

Thanks so much! I guess if I wanted to add dates, I would just add that colum.

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2160
  • Total likes: 363
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1593 on: July 06, 2023, 11:19:54 AM »
Thanks so much! I guess if I wanted to add dates, I would just add that colum.
Correct.

To note, the typical way most system/institutions structure such spreadsheet is by having one column for changes/expenses/withdraws and another one next to it for deposits/payments.



Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 524
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1594 on: July 06, 2023, 11:25:48 AM »
Correct.

To note, the typical way most system/institutions structure such spreadsheet is by having one column for changes/expenses/withdraws and another one next to it for deposits/payments.


how do I do that?

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2160
  • Total likes: 363
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1595 on: July 06, 2023, 11:26:31 AM »

Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 524
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1596 on: July 06, 2023, 11:43:24 AM »
Which part?
how do I make the running total match both the charge column and the payment column?

Offline Yisroel Tech

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Dec 2015
  • Posts: 2160
  • Total likes: 363
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
  • Location: Monsey, NY
Re: Excel Help and Problems
« Reply #1597 on: July 06, 2023, 11:46:13 AM »
how do I make the running total match both the charge column and the payment column?

In cell D2 enter formula (to simply copy the first number of the Charges/Payments:
Code: [Select]
=B2-C2Then in D3 use this formula to continue the running totals for all rows:
Code: [Select]
=IF(AND(B3="",C3=""),"",SUM(D2,B3-C3))
« Last Edit: July 06, 2023, 11:49:22 AM by Yisroel Tech »

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12811
  • Total likes: 3253
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1598 on: July 06, 2023, 11:48:27 AM »
if you ever insert or delete rows, make sure to check that your formulas are still referring to the right places
Workflowy. You won't know what you're missing until you try it.

Offline joe1234

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2015
  • Posts: 2061
  • Total likes: 524
  • DansDeals.com Hat Tips 19
    • View Profile
Re: Excel Help and Problems
« Reply #1599 on: July 06, 2023, 11:54:48 AM »
In cell D2 enter formula (to simply copy the first number of the Charges/Payments:
Code: [Select]
=B2-C2Then in D3 use this formula to continue the running totals for all rows:
Code: [Select]
=IF(AND(B3="",C3=""),"",SUM(D2,B3-C3))
only works for the 3rd row but not for the forth and on.