Sumifs, averageifs, countifs
 

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

Sumifs, averageifs, countifs
 

Try it like this:

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


"tgavin" wrote:

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

Sumifs, averageifs, countifs
 

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,"Prod uction") ))

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


"tgavin" wrote in message
...
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

Sumifs, averageifs, countifs
 

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

"Bernard Liengme" wrote:

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,"Prod uction") ))

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


"tgavin" wrote in message
...
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

Sumifs, averageifs, countifs
 

or you could use like this

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

"Teethless mama" wrote:

Try it like this:

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


"tgavin" wrote:

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

Sumifs, averageifs, countifs
 

Hello,

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

Regards,
Bernd

Sumifs, averageifs, countifs
 

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,"Prod uction") ))
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

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



"tgavin" wrote in message
...
Thanks! the notes I had didn't explain that and thanks for the new way to
look at SumProduct!

"Bernard Liengme" wrote:

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,"Prod uction") ))

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


"tgavin" wrote in message
...
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