Hi again.
So I have come a long ways with the help of many of you.
I have yet, another question.
So I have my numbers counted and organized nicely in multiple sheets.
Now, I am creating a master sheet. Adding up all my numbers from each sheet.
On the master sheet.
I made a small little table/grid.
It consists of.
What numbers there are.
How many times this number occurs in each column.
How many time this number occurs overall through out all the columns.
I would assume it's just primarily utilizing the SUM formula?
My problem is,
In each sheet it says.
for example,
03.) 87 times
04.) 22 times
05.) 103 times
etc.
So when I use the SUM formula to get the data from each sheet, it gives me an error.
Is there a way to add just the actual amount of times from the specified cell in each sheet?
like, ignore the "03.)" and "times" and just add the 87?
i'll attach an example so you can see what I mean.
-Mike
Last edited by rygotype; 03-17-2010 at 05:29 PM.
You need to split the cell out using this:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,FIND(" ",A1,1)+1,FIND(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255),A1,1)-2-FIND(" ",A1,1)))
and copy down for all of your 01)....etc then for each of those cells next to those use =Value(A1) and copy down then you can use =SUM(C1:Cxx) assuming the =Value(x) is in column C.
But you would be much better off redesigning the worksheet and having the values in different cells.
If the values are in A1 put this in B1 and copy down
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,FIND(" ",A1,1)+1,FIND(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255),A1,1)-2-FIND(" ",A1,1)))
In C1 add this and copy down
=Value(A1)
then you can use =SUM(C1:Cxx) to add all the values
I don't understand what you mean.
Could you please explain better?
Perhaps an example if that's not too much work?
-mike
Isn't there a way to just grab the actual value?
If you look at the non-highlighted block of numbers on sheet 1 in the example workbook i attached to my original post for example.
The formula is:
The highlighted block formula is:Code:=IF(COUNTIF(G$1:G1, G1)>1, "", TEXT(G1," 00.) ") & TEXT(COUNTIF(A:A, G1)," 00 ") & "times ")
There's got to be a way to grab the values from the highlighted block without taking the redundant information surrounding the numbers on the left and right sides.Code:=IF(ROWS(S$1:S1)<=SUM(N(M$1:M$5<>"")),INDEX(M$1:M$5,MATCH(SMALL(COUNTIF(M$1:M$5,"<="&M$1:M$5),SUM(N(M$1:M$5=""))+ROWS(M$1:M1)),COUNTIF(M$1:M$5,"<="&M$1:M$5),0)),"")
For example again.
03.) 07 times
04.) 02 times
05.) 10 times
There has got to be a way to only grab the "07" and ignore the "03.)" and the "times".
Has anyone looked at this with some detail yet?
I know,
@Simon Lloyd
Took some time and replied with some information, but I do not understand. From what it sounds, he thinks it's not possible, or just really hard or something?
More help on this topic would be appreciated!
Thanks guys!
-Mike
Any progress on this thread by chance?
Not much luck with this thread...
I took the formula you provided:
Ran a find & replace for A1 since my values are actually located in S1:Code:=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,"",MID(A1,FIND(" ",A1,1)+1,FIND(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255),A1,1)-2-FIND(" ",A1,1)))
Now, I pasted the formula next to the highlighted block of numbers (Which would specifically be Y1) - (from my example workbook provided and I get this error:Code:=IF(LEN(S1)-LEN(SUBSTITUTE(S1," ",""))=1,"",MID(S1,FIND(" ",S1,1)+1,FIND(MID(S1,FIND("~",SUBSTITUTE(S1," ","~",LEN(S1)-LEN(SUBSTITUTE(S1," ",""))))+1,255),S1,1)-2-FIND(" ",S1,1)))
Not sure what to do here??Code:#VALUE!
Is my request possible, or is this just a lost cause?
It would help if you'd explain what you're trying to do -- not what you want some formulas to do, but rather the whole big picture. As Simon says, your workbook needs to be redesigned, and no one can help with that without knowing a lot more.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
You won't get very far with that formula that i supplied as the cell you are looking to split is in itself a formula rather than text and that formula is based on another formula which creates the concatenated text.
Do yourself a favour and start afresh with your workbook design, ask specific questions here on how to acheive the look and feel of how you want the workbook to be.
To ask a silly question...
Are we to assume that per your sample the values in A:E (ie source for M:Q, S:W) are to be ignored - ie they do not really exist and are being used merely to generate the sample values ?)
Conducting conditional calculations in 3D (irrespective of embedded string complexity) is in itself a relatively complex task.
It is (invariably) better to calculate on a sheet by sheet basis (via intermediate summary table(s)) and simply sum the sheet by sheet records for the "3D" total.
Assuming:
a) A:E on each sheet to be non-existent in reality (if they do exist - use a COUNTIF table rather than working with S:W)
b) value range is 0-9
c) your sample file as basis for remainder of post...
You "could" do the following:
Group Sheets 1 & 2
Highlight Y1:Y10
Click into the Formula Bar into which paste the below:
=ROWS(Y$1:Y1)-1
press CTRL + ENTER to confirm
Highlight Z1:AD10
Click into the Formula Bar into which paste the below:
=SUMPRODUCT(--(LEFT(S$1:S$5,4)=TEXT($Y1," 00.")),--(0&SUBSTITUTE(SUBSTITUTE(REPLACE(S$1:S$5,1,FIND(")",S$1:S$5&")"),""),"times","")," ","")))
press CTRL + ENTER to confirm
Now select sheet Total (doing this will also ungroup the sheets).
Highlight range B2:F11
Click into the Formula Bar into which past the below:
=SUM(Sheet1:Sheet2!Z1)
press CTRL + ENTER to confirm
G2:G11 will be a simply SUM of B:F
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Er... forgive me for interjecting, but the following formula would strip out the number from M1:
=value(mid(trim(M1),5,Len(M1)-13))
It assumes that the initial number section will be no larger than 4 characters e.g. "99.)" and that the end caracters will be no more than 5 characters e.g. "times" after stripping off the beginning/trailing spaces.
Re: Adding data from multiple sheets
@minchazo
Thank you very much for taking the time to figure this out and reply to my post. This has solved my issue. It was exactly what I asked for.
Thanks a ton!!!
Many props!
SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks