Count dollars in related cells
 

This is not quite the scenario but it will explain a lot easier what I am
trying to do.

In a spreadsheet I am keeping a record of the sales figures (in dollars) in
a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April, July,
December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is Dollars.

To make things just a little more difficult there may be a number of times
that dollars are entered for a particular store for that given month eg
Store 1 may have 3 separate entries for January and 6 separate entries for
July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top row).
Where a store number and a month match with the spreadsheet above I want the
TOTAL dollars (for that store for that month) shown in that cell. Remember
there may be more than one entry for that store for that month.

What is a way for the new spreadsheet to look at the original spreadsheet
and enter the dollars automatically?

regards

PeterH

Count dollars in related cells
 

My View wrote:
This is not quite the scenario but it will explain a lot easier what
I am trying to do.

In a spreadsheet I am keeping a record of the sales figures (in
dollars) in a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April,
July, December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is
Dollars.
To make things just a little more difficult there may be a number of
times that dollars are entered for a particular store for that given
month eg Store 1 may have 3 separate entries for January and 6
separate entries for July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top
row). Where a store number and a month match with the spreadsheet
above I want the TOTAL dollars (for that store for that month) shown
in that cell. Remember there may be more than one entry for that
store for that month.
What is a way for the new spreadsheet to look at the original
spreadsheet and enter the dollars automatically?

regards

PeterH

Hi Peter,

I assume an example input range A1:C15, with labels in row 1, so data start
in row 2; column A is for stores, B is for month and C is for sales.



With this situation you can use a very simple pivot table, or if you like a
formula approach, this formula:

=SUMPRODUCT(($A$2:$A$15=$F2)*($B$2:$B$15=G$1)*$C$2 :$C$15)


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Count dollars in related cells
 

I am assuming that you are using true dates, even in the summary

In B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$200=$A2),--(MONTH(Sheet1!$B$2:$B$200)=MONTH(B$
1)),Sheet1!$C$2:$C$200)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"My View" <no spam wrote in message
...
This is not quite the scenario but it will explain a lot easier what I am
trying to do.

In a spreadsheet I am keeping a record of the sales figures (in dollars)
in
a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April,
July,
December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is Dollars.

To make things just a little more difficult there may be a number of times
that dollars are entered for a particular store for that given month eg
Store 1 may have 3 separate entries for January and 6 separate entries for
July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top
row).
Where a store number and a month match with the spreadsheet above I want
the
TOTAL dollars (for that store for that month) shown in that cell. Remember
there may be more than one entry for that store for that month.

What is a way for the new spreadsheet to look at the original spreadsheet
and enter the dollars automatically?

regards

PeterH

Count dollars in related cells
 

Assuming in your sales spreadsheet the dates are in DD/MM/YYYY (MM/DD/YYYY)
format:

In your "Summary" Sheet, put store names in column A, starting row 2. Enter
dates in B1 onwards as 01/01/2006 (Jan '06), 01/02/2006 (Feb' 2006) etc (to
column M) and format as "mmm-yy" (or whatever suits).

In cell B2 enter the following:

=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A2),--(MONTH(Sheet1!$B$2:$B$1000)=MONTH(Sheet2!B$1))*(Sh eet1!$C$2:$C$1000))

and copy across and down as required.

Sheet1 is your "Sales" sheet. Change ranges to suit but all ranges must be
same size.


HTH


"My View" wrote:

This is not quite the scenario but it will explain a lot easier what I am
trying to do.

In a spreadsheet I am keeping a record of the sales figures (in dollars) in
a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April, July,
December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is Dollars.

To make things just a little more difficult there may be a number of times
that dollars are entered for a particular store for that given month eg
Store 1 may have 3 separate entries for January and 6 separate entries for
July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top row).
Where a store number and a month match with the spreadsheet above I want the
TOTAL dollars (for that store for that month) shown in that cell. Remember
there may be more than one entry for that store for that month.

What is a way for the new spreadsheet to look at the original spreadsheet
and enter the dollars automatically?

regards

PeterH

Count dollars in related cells
 

John,

The * is not needed, numbers do not need coercing.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Toppers" wrote in message
...
Assuming in your sales spreadsheet the dates are in DD/MM/YYYY
(MM/DD/YYYY)
format:

In your "Summary" Sheet, put store names in column A, starting row 2.
Enter
dates in B1 onwards as 01/01/2006 (Jan '06), 01/02/2006 (Feb' 2006) etc
(to
column M) and format as "mmm-yy" (or whatever suits).

In cell B2 enter the following:


=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A2),--(MONTH(Sheet1!$B$2:$B$1000)=MONTH(
Sheet2!B$1))*(Sheet1!$C$2:$C$1000))

and copy across and down as required.

Sheet1 is your "Sales" sheet. Change ranges to suit but all ranges must
be
same size.


HTH


"My View" wrote:

This is not quite the scenario but it will explain a lot easier what I
am
trying to do.

In a spreadsheet I am keeping a record of the sales figures (in dollars)
in
a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April,
July,
December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is
Dollars.

To make things just a little more difficult there may be a number of
times
that dollars are entered for a particular store for that given month eg
Store 1 may have 3 separate entries for January and 6 separate entries
for
July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top
row).
Where a store number and a month match with the spreadsheet above I want
the
TOTAL dollars (for that store for that month) shown in that cell.
Remember
there may be more than one entry for that store for that month.

What is a way for the new spreadsheet to look at the original
spreadsheet
and enter the dollars automatically?

regards

PeterH

Count dollars in related cells
 

Thanks Bob ... it's a "bad" habit I have (but it's harmless)!

"Bob Phillips" wrote:

John,

The * is not needed, numbers do not need coercing.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Toppers" wrote in message
...
Assuming in your sales spreadsheet the dates are in DD/MM/YYYY
(MM/DD/YYYY)
format:

In your "Summary" Sheet, put store names in column A, starting row 2.
Enter
dates in B1 onwards as 01/01/2006 (Jan '06), 01/02/2006 (Feb' 2006) etc
(to
column M) and format as "mmm-yy" (or whatever suits).

In cell B2 enter the following:


=SUMPRODUCT(--(Sheet1!$A$2:$A$1000=$A2),--(MONTH(Sheet1!$B$2:$B$1000)=MONTH(
Sheet2!B$1))*(Sheet1!$C$2:$C$1000))

and copy across and down as required.

Sheet1 is your "Sales" sheet. Change ranges to suit but all ranges must
be
same size.


HTH


"My View" wrote:

This is not quite the scenario but it will explain a lot easier what I
am
trying to do.

In a spreadsheet I am keeping a record of the sales figures (in dollars)
in
a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April,
July,
December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is
Dollars.

To make things just a little more difficult there may be a number of
times
that dollars are entered for a particular store for that given month eg
Store 1 may have 3 separate entries for January and 6 separate entries
for
July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top
row).
Where a store number and a month match with the spreadsheet above I want
the
TOTAL dollars (for that store for that month) shown in that cell.
Remember
there may be more than one entry for that store for that month.

What is a way for the new spreadsheet to look at the original
spreadsheet
and enter the dollars automatically?

regards

PeterH

Count dollars in related cells
 

Sorry, the "months" are actually years from 2004 to 2029 but I thought it
easier to explain using the "months" analogy - must have been a late night
when I sent the post :).

To explain the real situation would be too confusing.

In that case, please assume "months" are text only and not calendar months.

I will test out the suggested formula soon and will get back to the group.

thanks for the quick feedback and suggestions

PeterH


"Bob Phillips" wrote in message
...
I am assuming that you are using true dates, even in the summary

In B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$200=$A2),--(MONTH(Sheet1!$B$2:$B$200)=MONTH(B$
1)),Sheet1!$C$2:$C$200)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"My View" <no spam wrote in message
...
This is not quite the scenario but it will explain a lot easier what I am
trying to do.

In a spreadsheet I am keeping a record of the sales figures (in dollars)
in
a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April,
July,
December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is Dollars.

To make things just a little more difficult there may be a number of
times
that dollars are entered for a particular store for that given month eg
Store 1 may have 3 separate entries for January and 6 separate entries
for
July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top
row).
Where a store number and a month match with the spreadsheet above I want
the
TOTAL dollars (for that store for that month) shown in that cell.
Remember
there may be more than one entry for that store for that month.

What is a way for the new spreadsheet to look at the original spreadsheet
and enter the dollars automatically?

regards

PeterH

Count dollars in related cells
 

Thanks Franz
I will test your formula and get back as soon as possible. Looks promising
though.
Thanks again for the feedback.
regards
PeterH



"Franz Verga" wrote in message
...
My View wrote:
This is not quite the scenario but it will explain a lot easier what
I am trying to do.

In a spreadsheet I am keeping a record of the sales figures (in
dollars) in a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April,
July, December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is
Dollars.
To make things just a little more difficult there may be a number of
times that dollars are entered for a particular store for that given
month eg Store 1 may have 3 separate entries for January and 6
separate entries for July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top
row). Where a store number and a month match with the spreadsheet
above I want the TOTAL dollars (for that store for that month) shown
in that cell. Remember there may be more than one entry for that
store for that month.
What is a way for the new spreadsheet to look at the original
spreadsheet and enter the dollars automatically?

regards

PeterH

Hi Peter,

I assume an example input range A1:C15, with labels in row 1, so data
start in row 2; column A is for stores, B is for month and C is for
sales.



With this situation you can use a very simple pivot table, or if you like
a formula approach, this formula:

=SUMPRODUCT(($A$2:$A$15=$F2)*($B$2:$B$15=G$1)*$C$2 :$C$15)


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Count dollars in related cells
 

Franz
That worked beautifully.
Just one more thing. If the cell has a zero dollar value what do I add to
the formula so that the cell will show as a blank cell. At the moment it
shows "$0". I would like to remove all "$0" values from cells.
thanks again
Bellissimo :)
PeterH



"Franz Verga" wrote in message
...
My View wrote:
This is not quite the scenario but it will explain a lot easier what
I am trying to do.

In a spreadsheet I am keeping a record of the sales figures (in
dollars) in a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March, April,
July, December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September, October.
etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is
Dollars.
To make things just a little more difficult there may be a number of
times that dollars are entered for a particular store for that given
month eg Store 1 may have 3 separate entries for January and 6
separate entries for July etc etc.

I now want to set-up a spreadsheet that shows the store name down the
left-hand side (ie left column) and the months across the top (ie top
row). Where a store number and a month match with the spreadsheet
above I want the TOTAL dollars (for that store for that month) shown
in that cell. Remember there may be more than one entry for that
store for that month.
What is a way for the new spreadsheet to look at the original
spreadsheet and enter the dollars automatically?

regards

PeterH

Hi Peter,

I assume an example input range A1:C15, with labels in row 1, so data
start in row 2; column A is for stores, B is for month and C is for
sales.



With this situation you can use a very simple pivot table, or if you like
a formula approach, this formula:

=SUMPRODUCT(($A$2:$A$15=$F2)*($B$2:$B$15=G$1)*$C$2 :$C$15)


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Count dollars in related cells
 

My View wrote:
Franz
That worked beautifully.
Just one more thing. If the cell has a zero dollar value what do I
add to the formula so that the cell will show as a blank cell. At the
moment it shows "$0". I would like to remove all "$0" values from
cells. thanks again
Bellissimo :)
PeterH


Hi Peter,

just incapsulate the SUMPRODUCT formula inside an IF function, in this way:

=IF(SUMPRODUCT(($A$2:$A$15=$F2)*($B$2:$B$15=G$1)*$ C$2:$C$15)=0,"",SUMPRODUCT(($A$2:$A$15=$F2)*($B$2: $B$15=G$1)*$C$2:$C$15))




"Franz Verga" wrote in message
...
My View wrote:
This is not quite the scenario but it will explain a lot easier what
I am trying to do.

In a spreadsheet I am keeping a record of the sales figures (in
dollars) in a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March,
April, July, December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September,
October. etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is
Dollars.
To make things just a little more difficult there may be a number of
times that dollars are entered for a particular store for that given
month eg Store 1 may have 3 separate entries for January and 6
separate entries for July etc etc.

I now want to set-up a spreadsheet that shows the store name down
the left-hand side (ie left column) and the months across the top
(ie top row). Where a store number and a month match with the
spreadsheet above I want the TOTAL dollars (for that store for that
month) shown in that cell. Remember there may be more than one
entry for that store for that month.
What is a way for the new spreadsheet to look at the original
spreadsheet and enter the dollars automatically?

regards

PeterH

Hi Peter,

I assume an example input range A1:C15, with labels in row 1, so data
start in row 2; column A is for stores, B is for month and C is for
sales.



With this situation you can use a very simple pivot table, or if you
like a formula approach, this formula:

=SUMPRODUCT(($A$2:$A$15=$F2)*($B$2:$B$15=G$1)*$C$2 :$C$15)


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Count dollars in related cells
 

Thank you again Franz

"Franz Verga" wrote in message
...
My View wrote:
Franz
That worked beautifully.
Just one more thing. If the cell has a zero dollar value what do I
add to the formula so that the cell will show as a blank cell. At the
moment it shows "$0". I would like to remove all "$0" values from
cells. thanks again
Bellissimo :)
PeterH


Hi Peter,

just incapsulate the SUMPRODUCT formula inside an IF function, in this
way:

=IF(SUMPRODUCT(($A$2:$A$15=$F2)*($B$2:$B$15=G$1)*$ C$2:$C$15)=0,"",SUMPRODUCT(($A$2:$A$15=$F2)*($B$2: $B$15=G$1)*$C$2:$C$15))




"Franz Verga" wrote in message
...
My View wrote:
This is not quite the scenario but it will explain a lot easier what
I am trying to do.

In a spreadsheet I am keeping a record of the sales figures (in
dollars) in a number of stores for different months of the year.

For example Store 1 may have sale records only for Jan, March,
April, July, December.
Store 2 may be records only for Jan, May, July, Aug, November.
Store 3 records cover only Feb, March, June, July, September,
October. etc etc

I have 3 columns - Col 1 is Store name, Col 2 is Month, Col 3 is
Dollars.
To make things just a little more difficult there may be a number of
times that dollars are entered for a particular store for that given
month eg Store 1 may have 3 separate entries for January and 6
separate entries for July etc etc.

I now want to set-up a spreadsheet that shows the store name down
the left-hand side (ie left column) and the months across the top
(ie top row). Where a store number and a month match with the
spreadsheet above I want the TOTAL dollars (for that store for that
month) shown in that cell. Remember there may be more than one
entry for that store for that month.
What is a way for the new spreadsheet to look at the original
spreadsheet and enter the dollars automatically?

regards

PeterH

Hi Peter,

I assume an example input range A1:C15, with labels in row 1, so data
start in row 2; column A is for stores, B is for month and C is for
sales.



With this situation you can use a very simple pivot table, or if you
like a formula approach, this formula:

=SUMPRODUCT(($A$2:$A$15=$F2)*($B$2:$B$15=G$1)*$C$2 :$C$15)


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy