Counting and Summing Techniques

I spend quite a bit of time reading the Excel newsgroups on the Internet, and it seems that many of the questions deal with conditional counting or summing. In an attempt to answer most of these questions, I present a number of formula examples that deal with counting various things on a worksheet, based on single or multiple criteria. You can adapt these formulas to your own needs.

NEW Excel 2007 includes two new counting and summing functions that aren't available in previous versions (COUNTIFS and SUMIFS). Therefore, I present two versions of some formulas: an Excel 2007-only version and an array formula that works with all recent versions of Excel.

Figure 3-8 shows a simple worksheet to demonstrate the formulas that follow. The following range names are defined:

su Mor

Hi 1 mill [W.lprt*.

. i =i H^ryi

i Cvrf«IS«4iwlwtrj«>IH

J Cw)-

M IMH-I Olfta

1 K =!■+,■ >n •- V h vr*ri

I .1 Pti->i. ¡fui Iont r Li- «lin "Y

L

1. ivl .1 »o * Ns s r-1 WL, 'JW

1

1 -. .v , 1 ¡.ji-yMUq*! TÉif

>

! C*** M* ■ AW Hi j ' v 'SMk'

m ¿-n Uw Hi M JU

_ '.■_'. LI— =1 S1+: ¡T+ifi'-'jn.-jü

■ .V ivm 1'hih.rUr ', r'ljf V jr

.JO

IB^-llUh^plHI

MS

Uü S'l' rj»h —h LVTi" ■ JJ 1 -.O r-Jrt ■ .■-■-

JOT

r {* if*** ' jj 1

UW

lis tediAl 1 HqaWi

Figure 3-8: This simple worksheet demonstrates some useful formulas for counting and summing.

CD- This workbook (including the formula examples) is available on the companion CD-ROM. ROM The file is named '■■*■ counting and summing examples . xlsx.

0 0

Post a comment