Theory of CountIF() vs SumProduct()

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

Numerous postings discuss issues re: various "counting" procedures.

SUMPRODUCT()
I do not understand why multiplying two columns together will
assist the count procedure unless it is necessary to establish a
mathematical pre-condition.

COUNTIF() This seems more straight forward

Can someone supply complete examples of both and plain-talk
theory as to which one is better?

Thanks!

Dennis
 
Hi Dennis

i'll try to give some answers (though a lot of others will eventually
provide mor detail)..
1. If you only have one condition, COUNTIF is straight forward
2. If you have more than one condition, COUNTIF does not work.
Therefore other solutions have been developed
2.a. SUMPRODUCT for conditions combined with AND: By multiplying two
conditions, e.g. =SUMPRODUCT((D2:D7<>"")*(G2:G7<>"")) Excel will
evaluate True conditions to '1'. So if both conditions are True, the
result is '1*1'. This will be added for all row. As a result you get
the correct count.
2.b. SUMPRODUCT for conditions combined with OR: By multiplying two
conditions, e.g. =SUMPRODUCT((D2:D7<>"")+(G2:G7<>""))
3. Of couirse you can achieve this also with a combination of SUM / IF
formulars enteres as array formular (CTRL+SHIFT+ENTER). Both will
achieve the same
3.a. =SUM(IF(AND(D2:D7<>"",G2:G7<>""), 1,0))
3.b. =SUM(IF(OR(D2:D7<>"",G2:G7<>""), 1,0))

The main difference between 2. and 3. are that SUMPRODUCT has not to be
entered as array formula.

In addition some web links
http://www.mvps.org/dmcritchie/excel/sumdata.htm
http://j-walk.com/ss/excel/tips/tip74.htm

HTH
Frank
 
Use countif if possible, but since many posters ask for countif with
multiple conditions which don't exist
thus a workaround is using sumproduct. There is a drawback with countif
since it won't differentiate between
text numbers and real numbers.

What you basically do with a formula like

=SUMPRODUCT((A2:A10="x")*(B2:B10="y"))

each array will return Boolean true or false

=SUMPRODUCT(({TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})*({TRUE;F
ALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}))

when you calculate by for instance multiplying a Boolean value with another
it will return 1 or 0 so when you multiply
the above arrays of true and false they will return 1 where both (each
adjacent cell) arrays return true,
in the above example it would be the first and the fifth, thus it would look
like

=SUMPRODUCT({1;0;0;0;1;0;0;0;0})

then sumproduct just sum all the 1s and the count where A is "x" AND B is
"y" is 2
You can't do that with countif.. The only time you can use 2 conditions is
when you want
to count between. What if I want to count all value greater than 2 and less
than 5

=COUNTIF(A1:A10,">2")-COUNTIF(A1:A10,">=5")

is probably preferable to

=SUMPRODUCT((A1:A10>2)*(A1:A10<5))
 
And just to give you another example:-

The formula exploits the fact that Excel interprets TRUE as 1 and FALSE
as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A")*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products



If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A"))

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on
whether or not the criteria has been met on that row, and this is the same as
counting the number of records that meet your criteria. Imagine the above
tables without Column C, and the last one would look like the following:-

A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
 
Dennis said:
Numerous postings discuss issues re: various "counting" procedures.

SUMPRODUCT()
I do not understand why multiplying two columns together will
assist the count procedure unless it is necessary to establish a
mathematical pre-condition.

COUNTIF() This seems more straight forward

Can someone supply complete examples of both and plain-talk
theory as to which one is better?

Thanks!

Dennis
Click to expand...
 
Dennis said:
Numerous postings discuss issues re: various "counting" procedures.

SUMPRODUCT()
I do not understand why multiplying two columns together will
assist the count procedure unless it is necessary to establish a
mathematical pre-condition.

COUNTIF() This seems more straight forward

Can someone supply complete examples of both and plain-talk
theory as to which one is better?

Thanks!

Dennis
Click to expand...
 
Although I personally wouldn't choose this method over SumProduct, I feel it
should at least be mentioned to round out this discussion, as an alternative
approach.

For ease of comprehension, you could simply sum Countif's to attain totals
of numerous, differing, conditions:

=COUNTIF(A1:A10,"ABC")+COUNTIF(A1:A10,"DEF")+COUNTIF(A1:A10,"GHI")+COUNTIF(A
100:A110,"JKL")

I really don't believe that there is anything in XL that can be accomplished
in *only* one manner.
 
Hi RD,

note also that you can only use countif like in your example if the type is
OR and not AND,
i.e. you can use countif where the equivalent sumproduct formula is

=SUMPRODUCT((Range1="x")+(Range2="y" etc

not

=SUMPRODUCT((Range1="x")*(Range2="y" etc
 
Back
Top