CountIf and SumIf

  • Thread starter Thread starter Janet
  • Start date Start date
J

Janet

Row C:C contains # 1 - 15
Row D:D contains $ Amounts

In range C600 I want to count the number of cells in
column C with the # 1

The formula is CountIf(C:C,="1")

Now in Range C601, I want to Sum row D:D but only for
Cells from C:C that contain "1"

I was hoping that the formula was
=If(C:C = 1 , Sum D:D)

This does not work. Can someone please give me the proper
formula. I work in Excel '97 if that makes any difference
 
Hi Janet
I assume that in Column C you have entered the values as numbers. Then
try the following
1. Count cells
=COUNTIF($C:$C,1)

2. Sum cells
=SUMIF($C:$C,1,$D:$D)

HTH
Frank
 
Janet,

Try

=SUMIF(C:C,1,D:D)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi,

1) first of all, becarefull want we want to calculate
something in a column C:C including the result itself in
range C600 and C601

2) for sum function, we have to use sumif(C:C,"1",D:D)
or sumif(C1:C599,"1",D1:D599)

Regards, Michel
 
-----Original Message-----
Hi,

1) first of all, becarefull want we want to calculate
something in a column C:C including the result itself in
range C600 and C601

2) for sum function, we have to use sumif(C:C,"1",D:D)
or sumif(C1:C599,"1",D1:D599)

Regards, Michel
.
Sorry, - another part to this
Click to expand...

I also need and "And" Statement

What I want now is

CountIf (C1:C599,"1",D1:D599) and D:D > 0

I have tried

And (CountIf(C1:C599,"1",D1:D599), D:D > 0 )

Also Tried

(CountIf(And(C1:C599,"1",D1:D599, D:D > 0))

but I get False in the Destination Cell
 
Hi Janet

COUNIF (as well as SUMIF) only accept one condition. For more than one
you can use SUMPRODUCT.
For counting use
=SUMPRODUCT((C1:C599=1)*(D1:D599>0))

For calculating the sum use
=SUMPRODUCT((C1:C599=1)*(D1:D599>0)*(D1:D599))

HTH
Frank
 
Janet said:
I also need and "And" Statement

What I want now is

CountIf (C1:C599,"1",D1:D599) and D:D > 0

I have tried

And (CountIf(C1:C599,"1",D1:D599), D:D > 0 )

Also Tried

(CountIf(And(C1:C599,"1",D1:D599, D:D > 0))

but I get False in the Destination Cell
Click to expand...

COUNTIF (and SUMIF) are quite limited functions. You can only have
one-criterion, and you can only use the criterion range directly (not a
function of it). You need to move on to SUMPRODUCT. (In fact, I now use
SUMPRODUCT even where COUNTIF or SUMIF would do.)

=SUMPRODUCT((C1:C599=1)*(D1:D599>0))
 
-----Original Message-----
Hi Janet

COUNIF (as well as SUMIF) only accept one condition. For more than one
you can use SUMPRODUCT.
For counting use
=SUMPRODUCT((C1:C599=1)*(D1:D599>0))

For calculating the sum use
=SUMPRODUCT((C1:C599=1)*(D1:D599>0)*(D1:D599))

HTH
Frank





.
That did it.
Click to expand...
Thanks everyone for all your quick replies
 
-----Original Message-----
Hi Janet

COUNIF (as well as SUMIF) only accept one condition. For more than one
you can use SUMPRODUCT.
For counting use
=SUMPRODUCT((C1:C599=1)*(D1:D599>0))

For calculating the sum use
=SUMPRODUCT((C1:C599=1)*(D1:D599>0)*(D1:D599))

HTH
Frank





.
Sorry, One more thing.....
Click to expand...

When I use your formula it does just what I need.
However, I am having trouble understanding it.

For Example
=SUMPRODUCT((C1:C599=1)*(D1:D599>0))
Click to expand...

I've always thought of the * as a multiplication symbol
but obviously it has another purpose in this case. Can
you explain to me.

Thanks
 
Sorry, One more thing.....
When I use your formula it does just what I need.
However, I am having trouble understanding it.

For Example

I've always thought of the * as a multiplication symbol
but obviously it has another purpose in this case. Can
you explain to me.

Thanks
Click to expand...

Here is a re-post of Ken Wright's explanation for SUMPRODUCt which is the
most comprehensive I have seen

Regards

Bob

===========================================================================
As an example of how it works:-

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
 
Janet said:
When I use your formula it does just what I need.
However, I am having trouble understanding it.

For Example

I've always thought of the * as a multiplication symbol
but obviously it has another purpose in this case. Can
you explain to me.

Thanks
Click to expand...

Hi Janet
the * will multiply. But to explain this:
The first part of the formula (C1:C599=1) will return TRUE or FALSE. By
multiplying this with a value (D1:D599) Excel will convert TRUE/FALSE
to 1/0
SUMPRODUCT will add all single multiplications

HTH
Frank
 
When I use your formula it does just what I need.
However, I am having trouble understanding it.

For Example

I've always thought of the * as a multiplication symbol
but obviously it has another purpose in this case. Can
you explain to me.

Thanks
Click to expand...

It is a multiplication symbol.
(C1:C599=1) produces an array of 599 TRUE/FALSE values.
Similarly for (D1:D599>0).
These two arrays are then multiplied together element by element (i.e. each
pair of values at a time), producing a third array of 599 values.
SUMPRODUCT then adds these up to give the required result.
The point to note is that Excel converts TRUE into 1 and FALSE into 0 when
it is asked to multiply a pair of values. Thus the final array is 599
numbers, each of which is 1 or 0, which can be added up.

The "normal" syntax for SUMPRODUCT is
=SUMPRODUCT(array1,array2)
If C1:C599 and D1:D599 had contained numbers that we wanted to multiply in
pairs and then add up, we would have used
=SUMPRODUCT(C1:C599,D1:D599)
However, we wanted to see if the numbers in C1:C599 equalled 1, and if those
in D1:D599 exceeded 0. Thus, we had to work with arrays of boolean values
(TRUE/FALSE) rather than numbers, so we needed something to convert TRUE
into 1 and FALSE into 0, which multiplication will do.

We could have used instead
=SUMPRODUCT((C1:C599=1)*1,(D1:D599>0)*1)
or
=SUMPRODUCT(--(C1:C599=1),--(D1:D599>0))
 
Thanks once again to everyone!

I had never used SumProduct before and do not like to use
something that I am not sure of the process behind.

As always, the reponses on this sight are very helpful!
 
Back
Top