Excel Help and Problems

[Yo ssi]

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, "<>")

[aygart]

That should be a standard SUMIFS.

True

[avadah]

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.

[etech0]

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)

[etech0]

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.

[skyguy918]

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

[avadah]

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?

[skyguy918]

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.

[Yisroel Tech]

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.

[joe1234]

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.

[Yisroel Tech]

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

[skyguy918]

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.

[joe1234]

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.

[Yisroel Tech]

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.

[joe1234]

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?

[Yisroel Tech]

how do I do that?

Which part?

[joe1234]

Which part?

how do I make the running total match both the charge column and the payment column?

[Yisroel Tech]

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

[etech0]

if you ever insert or delete rows, make sure to check that your formulas are still referring to the right places

[joe1234]

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.