Sumifs, averageifs, countifs

[tgavin]

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,D623,"Sales") returns 166,000

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

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

Terri

 

[Teethless mama]

Try it like this:

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

 

[Bernard Liengme]

Hi Terri,
With respect, you have misunderstood the "multiple criteria" bit
This would work
=SUMIFS(E6:E23,D623,"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,D623,"Sales") + SUMIFS(E6:E23,D623,"Production")
of course, you could use SUMIF in place of SUMIFS here

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

best wishes

 

[tgavin]

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

 

[Teethless mama]

or you could use like this

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

 

[Bernd P]

Hello,

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

Regards,
Bernd

 

[Bernard Liengme]

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,((D623="Sales")+(D623,"Production") ))
The part ((D623="Sales")+(D623,"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 ((D623="Sales")+(D623,"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