Formula for a running total in one cell

[cdon1983]

I'm trying to create a survey page where answers can be be given to questions either yes or no. These answers will then be totalled on a summery page. The issue I'm having is trying to create a running total in one cell combining the current formula =COUNTIF(Homes!B3,"YES").

[Trevor G]

Can you upload a sample of the workbook or place a uploaded copy to something like dropbox and add a link into your thread. Someone can take a look and hopefully provide a solution.

[cdon1983]

Originally Posted by Trevor G

Can you upload a sample of the workbook or place a uploaded copy to something like dropbox and add a link into your thread. Someone can take a look and hopefully provide a solution.

I've attached the spread sheet I'm working on

[Pecoflyer]

Originally Posted by Trevor G

Can you upload a sample of the workbook or place a uploaded copy to something like dropbox and add a link into your thread. Someone can take a look and hopefully provide a solution.

Hi Trevor,
it is always best that attachments should be posted on the forum, not a link to Dropbox or similar.
External links are notoriously unsafe and cannot always be used by all members depriving the OP from valuable help ( some members log on from their workplace)
When posted on the forum, the file will also be available later for other seekers to find, whereas external files could be deleted

[Trevor G]

When you state a running total the formula you are using is only pointing to a single cell on a single sheet, what type of combination are you after?

[cdon1983]

The idea is for the user to enter the answers on the home tab using the drop down menus with either yes or no. These answers will then show as a number on the Group tab. This is far as I got... I was hoping to set up a button with a macro that would save the answers after the survey was complete and then wipe the answers ready for the next survey. The answers that a found on the Group tab would then be accumulated as more and more survey are completed. I hope that makes sense..

[Trevor G]

What I can suggest is to Add an extra row above the drop downs and use an IF Statement to =IF(B4="YES",1,0) you can always hide the column, then you simulate copying the cell then select the home sheet and simulate using Paste Special and Values and Paste Special and Add. Which will then add the value to the total. Finally look to clear the YES from the cell.

Sampel code is below:

PHP Code:

Sub AddToTotals()
'This is a demo only for one cell if this works for you
'Then it needs to be worked on for all the other cells.
    Sheets("Homes").Range("b3").Copy
    Sheets("Group").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
        :=False, Transpose:=False
 Sheets("Homes").Range("b4").ClearContents
 
End Sub 


[Trevor G]

Thanks for letting me know.