Count dollars in related cells

[My View]

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

 

[Franz Verga]

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

 

[Bob Phillips]

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)

 

[Toppers]

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

 

[Bob Phillips]

John,

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

--
HTH

Bob Phillips

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

 

[Toppers]

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

 

[My View]

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

 

[My View]

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

 

[My View]

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]

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))


--
(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

 

[My View]

Thank you again Franz

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))



--
(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