Prev: Do you want to replace the contents of
Next: Excel trace dependents functions broken
From: Shirl on 27 Feb 2010 00:17
I am trying to add the totals given in Columns C, E, G etc etc of each
suburb listed in columns B,d,F etc etc. When the formula finds the total is
also needs to give me the name in column 1 for that area. (Two areas given in
example but not all week)

Eg: Find "PNS Sound Hutt" in column B, & column D, add 2.0 (from column c)
& (1 from column e) then show John. I need to know how often each person
visits each suburb.

Name Suburb No Suburb No

A B C D E etc etc
John MONDAY 6AM TUESDAY 6AM
PNS Sound Hutt 2.0 PNS Petone 4.0
WW Queensgate 3.0 WW Queengate 2.0
PNS Petone 1.0 PNS Sound Hutt 1.0
PNS Petone 1.0


Robert MONDAY 6AM TUESDAY 6AM
PNS Sound Hutt 2.0 PNS Sound Hutt 4.0
NW Sound Hutt 2.0 CD Sound Hutt 1.0
CD Sound Hutt 1.0 NW Sound Hutt 2.0
PNS Sound Hutt 1.0


--
Shirl, NZ
From: Russell Dawson on 27 Feb 2010 02:20
Hi

Use conditional sum wizard. See formulas tab - solutions - conditional sum

I've just done this for John PNS Petone but you'll soon get the idea. I'm
not sure how you want to display your data. I've taken it that each day
there is a calc - you can extend this as you require.

The formula should read

=SUM(IF($B$2:$B$5="PNS Petone",$C$2:$C$5,0))

to see how often John visits PNS Petone on Monday

Watch out for your headers in C & E columns. The wizard cannot
differentiate them so you will need unique titles.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Shirl" wrote:

> I am trying to add the totals given in Columns C, E, G etc etc of each
> suburb listed in columns B,d,F etc etc. When the formula finds the total is
> also needs to give me the name in column 1 for that area. (Two areas given in
> example but not all week)
>
> Eg: Find "PNS Sound Hutt" in column B, & column D, add 2.0 (from column c)
> & (1 from column e) then show John. I need to know how often each person
> visits each suburb.
>
> Name Suburb No Suburb No
>
> A B C D E etc etc
> John MONDAY 6AM TUESDAY 6AM
> PNS Sound Hutt 2.0 PNS Petone 4.0
> WW Queensgate 3.0 WW Queengate 2.0
> PNS Petone 1.0 PNS Sound Hutt 1.0
> PNS Petone 1.0
>
>
> Robert MONDAY 6AM TUESDAY 6AM
> PNS Sound Hutt 2.0 PNS Sound Hutt 4.0
> NW Sound Hutt 2.0 CD Sound Hutt 1.0
> CD Sound Hutt 1.0 NW Sound Hutt 2.0
> PNS Sound Hutt 1.0
>
>
> --
> Shirl, NZ
 | 
Pages: 1
Prev: Do you want to replace the contents of
Next: Excel trace dependents functions broken