Sumifs, averageifs, countifs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tgavin - 04 Jul 2009 13:20 GMT

I am an excel trainer and I am trying to prep the new functions and I can't
get the ifs to work. If I use the functions for 1 criteria, it works, when I
add the 2nd, I get 0 for sumifs and countifs and #div/0! for averageifs.

=SUMIFS(E6:E23,D6:D23,"Sales") returns 166,000

=SUMIFS(E6:E23,D6:D23,"Sales",D6:D23,"Production") returns 0

I am following the tool tips, what am I doing wrong?

Terri

Reply to this Message

Teethless mama - 04 Jul 2009 13:34 GMT

Try it like this:

=SUM(SUMIFS(E6:E23,D6:D23,{"Sales","Production"}))

> I am an excel trainer and I am trying to prep the new functions and I can't
> get the ifs to work. If I use the functions for 1 criteria, it works, when I
[quoted text clipped - 7 lines]
>
> Terri

Reply to this Message

Teethless mama - 04 Jul 2009 14:26 GMT

or you could use like this

=SUM(SUMIF(D6:D23,{"Sales","Production"},E6:E23))

> Try it like this:
>
[quoted text clipped - 11 lines]
> >
> > Terri

Reply to this Message

Bernard Liengme - 04 Jul 2009 13:36 GMT

Hi Terri,
With respect, you have misunderstood the "multiple criteria" bit
This would work
=SUMIFS(E6:E23,D6:D23,"Sales",F6:F23,"Boston")
where we are looking at two columns

Your formula is looking at the same column twice. If you boss asks for a
list of customers who live in Boston and Seattle you can tell her
immediately that there are none - your data base has only one city per
customer so nobody has two cities of residence. Your boss should have said
OR not AND. Your SUMIFS is an AND when you want an OR. The D values cannot
equal two things at the same time.

a)=SUMIFS(E6:E23,D6:D23,"Sales") + SUMIFS(E6:E23,D6:D23,"Production")
of course, you could use SUMIF in place of SUMIFS here

b) =SUMPRODUCT(E6:E23,((D6:D23="Sales")+(D6:D23,"Production") ))

best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> I am an excel trainer and I am trying to prep the new functions and I
> can't
[quoted text clipped - 9 lines]
>
> Terri

Reply to this Message

tgavin - 04 Jul 2009 13:46 GMT

Thanks! the notes I had didn't explain that and thanks for the new way to
look at SumProduct!

> Hi Terri,
> With respect, you have misunderstood the "multiple criteria" bit
[quoted text clipped - 28 lines]
> >
> > Terri

Reply to this Message

Bernd P - 04 Jul 2009 18:27 GMT

Hello,

I suggest to take
http://sulprobil.com/html/pstat.html

Regards,
Bernd

Reply to this Message

Bernard Liengme - 04 Jul 2009 18:39 GMT

Thanks for the feedback

I am a retired teacher so I cannot resist teaching! So I am tempted to give
you more notes!

In =SUMPRODUCT(E6:E23,((D6:D23="Sales")+(D6:D23,"Production") ))
The part ((D6:D23="Sales")+(D6:D23,"Production"), get evaluated as two
arrays of Boolean values, as in something like
(TRUE, TRUE, FALSE, TRUE .....)+(TRUE, FALSE, FALSE, TRUE)

But when Excel sees math operations (like + or *) being done on Boolean, it
treats True as 1 and FALSE as zero. So we get
(1,1,0,1...)+(1,0,0,1...) which we will add to give 1,1,0,1
So the addition is equivalent to OR since (1+0), (0+1) and (1+1) always
make logical 1

But if we had ((D6:D23="Sales")+(D6:D23,"Production"),
We might get (1,1,0,1...)*(1,0,0,1...)
Which evaluates to 1, 0, 0, 1 because (1*1) is the only time we get 1, all
other combinations give 0. So multiplication is equivalent to AND

Here endth the second lesson,
best wishes

Signature

Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

> Thanks! the notes I had didn't explain that and thanks for the new way to
> look at SumProduct!
[quoted text clipped - 35 lines]
>> >
>> > Terri

Reply to this Message