Conditional Sum (97,2000)

Topic

New Reply

I know that Excel 97’s Conditional Sum Wizard does not work with Merged Cells. What about Excel
2000? Is there a way around this limitation. Have a 4000 line spreadsheet that has 3 rows per merged cell that I would like to do conditonal summing on.

Reply | Quote

Replies

Hi Thomas,

This may not answer your question, but may help somewhat!

Many of us Excel users dislike the Merge Cells facility because of the limitations it imposes in areas such as you have found, and when copying cells etc. What I prefer to do is to use Format, Cells, Alignment, and Centre across Selection. This has very much the desired effect of the Merge Cells command, by spreading a value across several cells, but does not have the limitations.

Good Luck!

Reply | Quote

Can you use the the Conditional Sum wizard before mereging the cells, and then once your formula is in place merge the cells.

If that does not help, you can build the formula manually. If you need some help with that post back with the details.

Andrew C

Reply | Quote

Unfortunately the formatting of spreadsheet is dictated by a report format. Is there a quick way to remove the merging, delete the empty rows, do the conditional summing, and then get format back to normal? In any case, being able to do conditional summing is a requirement, even if I have to do it
manually.

Reply | Quote

Probably your quickest way would be to set up some data on a blank worksheet which reflects the range and data you are working with, but without the merge formatting. Then use the wizard on that sheet to insert the formula in the corresponding cell that you are using on the working sheet.

When you are satisfied with the formula, copy it and paste it into the cell where you actually require the result to be.

When you have pasted the formula you will need to place the cursor in the formula bar and then press Ctrl-Shilft and Enter at the same time, as the formula will be an array formula. (i.e. it twill be enclosed in {}).

Andrew C

Reply | Quote

An option is not to use the Conditional Sum Wizard. Instead, try to devise the required formulas yourself directly where they are needed.

Reply | Quote