I am trying to figure out how to sum visits for various browser sizes. The browser sizes are displayed in a combination of text and number (eg, 600x1200). I want to count any visits with a screen size above 1000 (either height or width). Anyone know how to do this?
See attached.
Last edited by sam887; 06-25-2014 at 12:00 PM.
Welcome to the Forum.
Please don't upload images as images are not very helpful and don't expect anybody to type the data shown in the image into the excel sheet on your behalf.
Try this...... (not tested but arrange the data as per the ranges in the formula and test the formula to see whether you get the desired result.)
Assuming your Browser Sizes are in the range B4:B13 and Visits are in the range C4:C13 (as per your example image), then
Is this what you are trying to achieve?
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Took data from A1 to B3 in my sample.
Formula:
Please Login or Register to view this content.
Control+Shift+Enter
Last edited by haripopuri; 06-25-2014 at 01:08 PM. Reason: Forgot CSE
________________________________________________________
If your problem is solved, update the thread as SOLVED: Go to the top of the first post-Select Thread Tools-Select Mark thread as Solved OR - Go to the first post - Click edit- Click Advance- Just below the word "Title:" you will see a dropdown with the word No prefix.- Change to Solve- Click Save.
Show your gratitude to the person who helped you solving your problem by clicking on star button at the bottom of such post.
@sktneer,
This is amazing - I would have never gotten it. I wasn't able to make the exact formula work, however I tweaked it to only focus on a single cell. See here:
=SUMPRODUCT(--(LEFT(B9,FIND("x",B9)-1)*1>1000),C9)+SUMPRODUCT(--(RIGHT(B9,LEN(B9)-FIND("x",B9))*1>1000),C9)
Where B9 is the screen size and C9 is the number of visits. The actual data is a massive table, but I am having one other issue. If I set the browser size to >500, I find that it double counts the visits for the screen size. Is there a way to alter it so that if the output is greater than the actual visits, I can divide by two?
Or, more simply, is there a way to ensure that it does not double count visits if both the height and width are greater than any number I enter?
Thank you!!!
Oh yes. I didn't consider that what if both the height and width are greater than 1000 in a single cell? Very good pick...
In that case try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter. (i.e. after placing the formula in the cell hold down the Ctrl+Shift and then press Enter and if you paste the formula in the cell, first press F2 (function key) and then hold down Ctrl+Shift and then press Enter.)
See if you get the desired result.Please Login or Register to view this content.
Great stuff. Before I saw the reply I simply created a new column that with:
=IF(F11>C11,F11/2,IF(F11=C11,C11)
so that if the number is greater, it divides by 2 but if the number is the same, it displays that number. Not as elegant but it gets the job done.
Thanks for the help - this forum is great.
Here is a solution for you that takes the list of browser sizes and if there are duplicates creates a list of unique sizes and gives the total for all browser sizes that have a dimension of 1000 or more in either length, width or both.
The solution uses a helper column to determine the dimensions and returns. I used text values to make it obvious what the results were. You might prefer something else:
lessless if both dimensions are < 1000
lessgreater if the first dimension is less than 1000 and the second dimension is >=1000
greaterless if the first dimension is >1000 and second dimension is <1000
greatergreater if both dimensions >=1000
Helper column:
Formula:Please Login or Register to view this content.
Unique browser sizes:
Formula:Please Login or Register to view this content.
Count for browsers with a dimension >=1000
Formula:Please Login or Register to view this content.
Conditional formatting hides 0 values
Last edited by newdoverman; 06-25-2014 at 04:39 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks