Sumifs, averageifs, countifs

  • Thread starter Thread starter tgavin
  • Start date Start date
T

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,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
 
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
 
Thanks! the notes I had didn't explain that and thanks for the new way to
look at SumProduct!
 
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
 
Back
Top