998
Index
function of, 928
overview, 282
ranges with error values, 379–380
single array formula, 358
summing all cells in range, 299–300
“top n” values, 302
Sum summary formula, 108
sum_range argument, 302
SUMIF function
arguments, 302
function of, 928
overview, 282
summing values based on date comparison, 305
SUMIFS function, 282, 928
Summarize Values By tab, Value Field Settings dialog box, 707
summarizing formula data, 214–216
summary formulas
consistency, 585
Total Row, 108
summing
digits of integers, 386–387
every nth value, 388–390
formulas
all cells in a range, 299–300
computing a cumulative sum, 300–302
“top n” values, 302
n largest values in ranges, 381
ranges that contain errors, 379–380
rounded values, 388
smallest values in ranges, 372–373
SUMPRODUCT function, 282, 358–359, 928
SUMSQ function, 282, 928
SUMX2MY2 function, 282, 928
SUMX2PY2 function, 928
SUMXMY2 function, 282, 928
surface charts, 432–433
surface charts.xlsx sample file, 938
switching, Excel windows, 52
SYD function, 351, 924
Symbol dialog box, 233
symbols, hiding in ouline, 588
Symbols control, Equation Editor, 536
Synchronous Scrolling toggle, 61
syntax
PMT function, 331
PPMT function, 331
HLOOKUP function, 314
INDEX function, 316
LOOKUP function, 314
NPER function, 333
PV function, 333strings
converting from nondate to date, 261–262
counting characters in, 239
defined, 229
extracting all but the first word of, 245
extracting characters from, 240–241
extracting the first word of, 244
extracting the last word of, 244–245
number formatting, 557–558
removing non-numeric characters from, 390
searching and replacing within, 242–243
searching within, 242
splitting without using formulas, 247
Structured Query Language (SQL), 678
Structures control, Equation Editor, 536
Style dialog box
modifying named styles, 133
Protection option, 134
Style Gallery, 611
Style property, ComboBox control, 867
Style settings, custom templates, 170
styles
charts, 412–413
named
applying, 132–133
controlling with templates, 135
creating, 134
merging from other workbooks, 134–135
modifying, 133
overview, 131
pictures and drawings, 528–529
Styles object, 815
Sub procedure
debugging custom functions, 834
defined, 801
general discussion, 799–800
writing to display UserForm, 848
SUBSTITUTE function
counting number of words in cells, 248
function of, 932
replacing text, 241
substrings, 243–244
SUBTOTAL function, 282, 928
subtotals, pivot table, 705
subtraction operator, 658
SUM formula, 371–372
SUM function
array constants, 359
counting differences in two ranges, 384
displaying totals, 498–49960_474877-bindex.indd 99860_474877-bindex.indd 998 3/29/10 11:48 PM3/29/10 11:48 PM