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