LinkBack URL
About LinkBacks
Hello,
I want to find all positive and negative numbers in a row and copy them us numbers in one cell (as separate numbers).Alternatively in two cells (positive-negative)
I hope someone can help me out.
Thanks in advance!
Hello,
I want to find all positive and negative numbers in a row and copy them us numbers in one cell (as separate numbers).Alternatively in two cells (positive-negative)
I hope someone can help me out.
Thanks in advance!
In Cell J2, enter this formula:- then copy and paste the cell into Cell K2Formula:
Please Login or Register to view this content.
In Cell J3, enter this formula:- then copy and paste the cell into cell K3Formula:Please Login or Register to view this content.
Is that what you are looking for?
<<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts
Thank for your help
It works fine but i want to have the actual numbers in Cell K2 (=4,96+54,96+20+58+85)
Is your 2021 Excel 2021 or Excel 365 by any chance?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
This will give the values as text, but will not show the total value:
="="&LET(c,CONCAT(FILTER(D4:D17,(A4:A17=J1)*(D4:D17>=0))&"+"),LEFT(c,LEN(c)-1))
="="&LET(c,CONCAT(ABS(FILTER(D4:D17,(A4:A17=J1)*(D4:D17<0)))&"+"),LEFT(c,LEN(c)-1))
Maybe have separate columns for the value string and the total value?
Last edited by AliGW; 05-05-2023 at 05:13 AM.
No i want to sum the separate values (=4,96+54,96+20+58+85) -> 222.92
You cannot show the values in the cell and sum them using a formula. Might be possible with VBA - shall I move the thread for you?
ok maybe compine the two formulas to give the sum (positive and negative numbers based on A column ?
Better explaine -> I want to sum all numbers in 423 (April dates ) in K2
It will be huge help for me.
Did you try what I suggested?
See the attachment for it in situ.
yes but not helpful,i want the sum
But you said you wanted all of the numbers, and I have already told you that you cannot have both the numbers and the sum with a formula.
The closest I can get you is this:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
POSITIVE =4.96+54.96+20+58+85=222.92 NEGATIVE =30+32+112=-174
Sheet: Sheet1
BUT it's text and can't be used in any other calculation as it is.
="="&LET(c,CONCAT(FILTER(D4:D17,(A4:A17=J1)*(D4:D17>=0))&"+"),LEFT(c,LEN(c)-1))&"="&SUMIFS($F$4:$F$17, $F$4:$F$17, ">0", $A$4:$A$17, J$1)
="="&LET(c,CONCAT(ABS(FILTER(D4:D17,(A4:A17=J1)*(D4:D17<0)))&"+"),LEFT(c,LEN(c)-1))&"="&SUMIFS($F$4:$F$17, $F$4:$F$17, "<0", $A$4:$A$17, J$1)
If this is no good, then you will have to go with VBA.
Last edited by AliGW; 05-05-2023 at 05:47 AM. Reason: Typo fixed.
Out of curiosity, is there a specific reason why you are trying to show it like this instead of just using the answer?
If it's just to get a sense of how the data is flowing through, could you instead consider applying filters the table?
for AliGW #7 and #11 Maybe i wasnt clear.You said "You cannot show the values in the cell and sum them using a formula."Ok i can live with that
I want at least the sum
Maybe compine the two formulas to give the sum (positive and negative numbers based on A column ?
Better explaine -> I want to sum all numbers (positive and negatives based on 423 (April dates ) in j4 (18.59)
It will be huge help for me.
ps1 I dont know how to deal with yours suggestion in L12
ps2 English is not my native language please bear with me
for AskMeAboutExcel
The file is a bank account cashflow and i want in specific date to extract data in another workbook
Are you looking for something like this? :Book1_test - Copy.xlsx
Very close to what i need.But is it possible to have the result (one formula) in one cell (positive and negative numbers) based on date (423) ?
@AskMeAboutExcel
Administrative Note:
Please include formulae used in your post in addition to providing a workbook (for the benefit of members unable to download attachments).
Thanks.
Sure. Using the original location of the date in cell J1
For positives:
Formula:Please Login or Register to view this content.
For negatives:
Formula:Please Login or Register to view this content.
@jimapos - if I'm understanding you correctly now, you are looking for a method to automatically build the formula you typed in cell J2? If that's the case, the only way to do this is through VBA. Which is possible, but complex, and from your use case I still don't see why that would be useful.
I am really not sure what you want, but I suspect that it won't be possible with a formula, as I mentioned much earlier in the thread.
No idea what you mean by this - sorry.ps1 I dont know how to deal with yours suggestion in L12
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks