Excel Help and Problems

[Yisroel Tech]

only works for the 3rd row but not for the forth and on.

What is the exact formula you have now in the 4th row?

[joe1234]

What is the exact formula you have now in the 4th row?

don't know. how do I figure that out?
edit: it shows nothing.

[Yisroel Tech]

don't know. how do I figure that out?

When you select a cell, you can see/copy the formula from the formula bar on top of the sheet. Or double-click in the cell to be able to see/copy the formula.

edit: it shows nothing.

Didn't realize I need to specify it again, as seen in my screen recording earlier, after entering the formula in D3 you need to drag fill down the would column.

[joe1234]

When you select a cell, you can see/copy the formula from the formula bar on top of the sheet. Or double-click in the cell to be able to see/copy the formula.Didn't realize I need to specify it again, as seen in my screen recording earlier, after entering the formula in D3 you need to drag fill down the would column.

I double clicked and it shows nothing on the 4th row.

[joe1234]

Didn't realize I need to specify it again, as seen in my screen recording earlier, after entering the formula in D3 you need to drag fill down the would column.

I did that, but that only helped for the 4th row but not the 5th. Then I pressed enter and after every enter it took care of one one row. also when i put in the payments for example -20 it still added instead of minusing.

[Yisroel Tech]

I did that, but that only helped for the 4th row but not the 5th. Then I pressed enter and after every enter it took care of one one row. also when i put in the payments for example -20 it still added instead of minusing.

You did what? Dragged it down the column further than the 5th row and it didn't fill it?



As for the minus, in my new example (since the columns are separate) I used positive numbers to represent the amount deposited. So 20 instead of -20. If you want to use -20 you need to do in cell D2

Code: [Select]

=SUM(B2:C2)Then in D3 use this formula to continue the running totals for all rows:

Code: [Select]

=IF(AND(B3="",C3=""),"",SUM(D2,B3:C3))

[Yo ssi]

Wow kudos @Yisroel Tech for your patience!

[joe1234]

@Yisroel Tech it works great! thanks so much for all your help and patience!

[yoruel]

You did what? Dragged it down the column further than the 5th row and it didn't fill it?



As for the minus, in my new example (since the columns are separate) I used positive numbers to represent the amount deposited. So 20 instead of -20. If you want to use -20 you need to do in cell D2

Code: [Select]

=SUM(B2:C2)Then in D3 use this formula to continue the running totals for all rows:

Code: [Select]

=IF(AND(B3="",C3=""),"",SUM(D2,B3:C3))

@Yisroel Tech  how do share/ video the screen.to DDF

[Yisroel Tech]

@Yisroel Tech  how do share/ video the screen.to DDF

I screen record as GIF using ShareX (one of the most wonderful tools I found, it's loaded with goodies!) and then upload like any picture.

[avadah]

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 tried it and keep getting the "Not trying to type a formula?" error

[etech0]

I tried it and keep getting the "Not trying to type a formula?" error

did you try the DSUM I posted above?

[Yisroel Tech]

I tried it and keep getting the "Not trying to type a formula?" error

Which version of Excel do you use?

Also possible that your system "list separator" isn't a comma, then you need to change the commas in the formula to whatever list separator your system is using (you can see it in "Change date, time, or number formats" > Additional Settings, the List separator) or change Excel not to use the system one.

[avadah]

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.

It's working but i don't understand it. What's the 3 in field doing? I'd prefer a different way bec I don't want those extra cells visible and if I hide them somewhere i'm gonna forget about them.

[etech0]

It's working but i don't understand it. What's the 3 in field doing? I'd prefer a different way bec I don't want those extra cells visible and if I hide them somewhere i'm gonna forget about them.

which 3?
you can put those other cells in a different sheet in the file so you don't have to see them

[Yisroel Tech]

which 3?

He's asking about the 3 in the function's 2nd argument:
=DSUM(A:C,3,H1:I5)

[etech0]

He's asking about the 3 in the function's 2nd argument:
=DSUM(A:C,3,H1:I5)

Oh
It means that the column to sum is the 3rd column in the range (1st argument)

[avadah]

Oh
It means that the column to sum is the 3rd column in the range (1st argument)

Got it. Why does it need to alternate lines? Meaning have apple in fruit column next to apple in food column? How would I use it with the wild card feature meaning it would count apple, green apple and red apple?

[etech0]

Got it. Why does it need to alternate lines? Meaning have apple in fruit column next to apple in food column?

if they were in the same row it would join the criteria with AND, so it'd only find those that have apple in both, or orange in both. Separate rows tells it to join the criteria with OR so it'll run for any row that has apple in either column, or orange in either column

[avadah]

if they were in the same row it would join the criteria with AND, so it'd only find those that have apple in both, or orange in both. Separate rows tells it to join the criteria with OR so it'll run for any row that has apple in either column, or orange in either column

Thanks. Is there a way to use it with the wild card feature?