1. ## using SUMIF (xl97/xl2000)

Hi all,
I need to determine a total for a given cell where the criteria comes from a selection made in other cells. Clear?? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

Example: In English, the formula for C11 would be:

C11 = total of C3:C7 where B3:B7 = "Dog"

I have five items which need to be totaled for a period of 60 months. In the above example, "Dog" can be listed 0 or 5 times (and so can any of the other 4 items).

I attached a sample worksheet to make it more clear what I'm trying to do because I'm not quite able to explain it well enough in words. Maybe SUMIF isn't the right formula to use, but I'm not too familar with anything or than the trivial formulas.

Any thoughts would be appreciated.
Deb <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

2. ## Re: using SUMIF (xl97/xl2000)

=SUMIF(b3:b7,"=Dog",c3:c7)

3. ## Re: using SUMIF (xl97/xl2000)

Wow, what a response, I barely clicked "Post It" when you had the answer. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> You probably didn't even have to look at my sample,either.

I really gotta sit down one day soon and read Walkenbach's Excel2000 formula book....

Thnx again, Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

4. ## Re: using SUMIF (xl97/xl2000)

Yeah, excellent timing, wasn't it <bg>.

And you were right, I did not read the attachment, nor did I test the formula I suggested <g>.

5. ## Re: using SUMIF (xl97/xl2000)

I have had questions asked, usually by auditors, as to how do you know the answer is correct? Where i anticipate this may happen I take one of two approaches, which helps this vexed event.
1. use advanced filter to select only the records fitting the criteria, and have these copied to another part of the w/s, then sum.
2. Use an "if" formula to extract the number using criteria in a fixed cell, then summing the whole column.
Jan's formula is accurate and concise, but when you need to "prove" the answer, especially in a w/s with lots of data, then the above can be a quick way to provide the information without changes to the base data set.
<img src=/S/2cents.gif border=0 alt=2cents width=15 height=15><img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>

6. ## Re: using SUMIF (xl97/xl2000)

Good point.

A good spreadsheet habit is to build check formulas into your work, such as summing the sumifs and compare them to another formula which should result in the same answer. I often do this just to make sure I get it right in the first place, then leave the checks in the WB as evidence. An added benefit of this habit is that if you then perform some modifications and blow something subtle up, the cross-checks may warn you. Very few WB authors create this kind of self-checking.

And you can show the self-checks to the auditors.

Or, since I used to be an auditor (before dirt was created), tell them quite seriously "that's your job". <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•