| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I count conditionally formatted red cells in Excel 2000 | Excel Discussion (Misc queries) | |||
| Adding "and" to Spellnumber code | Excel Discussion (Misc queries) | |||
| Currency to Text | Excel Worksheet Functions | |||
| Count if cells have multiple data | Excel Worksheet Functions | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Hybrid Mode
