Excel Spreadsheet condition calculation help needed!

[kilo3000]

Hi
I would like to calculate some numbers in a table based on text contained in a cell beside it.

I have a spreadsheet with a huge listing of urls in one column and the number of visits to that page in a column beside it. I need to calculate the total number of visits in groups of pages according to the section they are under in the website navigation. So for example: add all the visits of pages with the URL that contains the page names(as part of the URL) "one.html" or "two.html" or "three.html".

I was wondering how I can do this? and should I use Visual Basic? or can I do it all with Excel? and can you give me an example of code I can use to perform the calculations I need.

Thanks!

 

[ETID]

Use the pivot table to group this data...
...under the "data" tool bar

It was designed for just this type of grouping and it has a wizard and it's not tough to learn.

Don't worry about screwing it up. it only reference your data and can be deleted and start over again and again without harming your data.

if you get stuck,...someone here can help you through it.

 

[kilo3000]

Thanks for the reply ETID
I'm going throught the wizard right now, but I dont know where I can specify the grouping I want for the data.

For example, the data is aranged like this...
one.html 10
two.html 12
a.html 5
three.html 6
c.html 3
d.html 15

one.html, two.html, and three.html are group1.
and a.html, b.html, c.html, and d.html are in group2.

 

[barbola]

You need a column for the Group Number (use vlookup) and then include that in your pivot table.

Thanks!
Barb E.

 

[NumberCrunchingMonky]

kilo:

I've felt your pain and this is how to cure it. The SUMPRODUCT formula further down is more of what your looking for, but I think you'll find this one useful too.

Here's how to count the URLs if they weren't subtotalled in the adjacent column. If you have to subtotal them in a separate operation to get the column with counts this would negate that:

Name your column with the URLs "URL"

=SUM((URL="one.html&quot+(URL="two.html&quot+(URL="three.html&quot)

The "+" operator represents "OR" ("AND" would be the "*" operator).

Or you could do it this way:
{=SUM(COUNTIF(URL,{"one.html","two.html","three.html"}))}

Note: physically type the {} brackets around "one.html", "two.html", "three.html" and press Shift+Ctrl+Enter to create the brackets around the whole formula (to make it an array). I just tried it and it works. Let me know if you have an issue.

Here's how to SUM them, being that there's already a count in an adjacent column. It's the SUMPRODUCT Formula and I think it's probably the most useful thing I use on a daily basis:

Name your column with URLs "URL"
Name your column of adjacent counts "Hits":

Here's the formula:
=SUMPRODUCT(((URL="one.html&quot+(URL="two.html&quot+(URL="three.html&quot)*(Hits))

It's important to group all the "OR" criteria in parentheses, so the formula basically says this:

"If 'one.html' OR 'two.html' OR 'three.html' is found , then return and sum the values in the adjacent column (Hits).

Have fun.

Monky