Another Countif??

[Hipsway]

I have a spreadsheet containing various fields which is used to monito
the amount of telephone calls logged by members of my department.
I've used the countif function to break this down to calls logged b
each individual. The next column I would like to add is the amount o
calls *completed* by each individual. The completed column onl
contains a true or false value.

So at the moment it would read:-

Name Calls Completed
JOHN 5 ???

Not sure if I've explained this well but what I'm aiming for is t
count Johns calls in one column(done) and in the next column count ho
many of those calls were completed(driving me mad)

Any help would be great. Thank

 

[Paul]

I have a spreadsheet containing various fields which is used to monitor
the amount of telephone calls logged by members of my department.
I've used the countif function to break this down to calls logged by
each individual. The next column I would like to add is the amount of
calls *completed* by each individual. The completed column only
contains a true or false value.

So at the moment it would read:-

Name Calls Completed
JOHN 5 ???

Not sure if I've explained this well but what I'm aiming for is to
count Johns calls in one column(done) and in the next column count how
many of those calls were completed(driving me mad)

Does your question simplify to something along the lines of wanting to count
the number of rows where the value in column A is "JOHN" and the value in
column B is TRUE? If so, try a formula such as
=SUMPRODUCT((A1:A100="JOHN")*B1:B100)

 

[Hipsway]

Unfortunately the SUMPRODUCT idea doesnt work, I get a #NUM! returned.
Tried changing trues to 1 and falses to 0 but that didn't help either.

 

[RagDyer]

What defines, designates, specifies, indicates, a completed call ?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


I have a spreadsheet containing various fields which is used to monitor
the amount of telephone calls logged by members of my department.
I've used the countif function to break this down to calls logged by
each individual. The next column I would like to add is the amount of
calls *completed* by each individual. The completed column only
contains a true or false value.

So at the moment it would read:-

Name Calls Completed
JOHN 5 ???

Not sure if I've explained this well but what I'm aiming for is to
count Johns calls in one column(done) and in the next column count how
many of those calls were completed(driving me mad)

Any help would be great. Thanks

 

[Otto Moehrbach]

If I'm reading you right, you want, in the third column, a True or False
telling if the call was completed. But the second column contains the
number of calls placed by John. This would make a True or False entry in
the third column invalid if column 2 contains a number greater than one. Am
I missing something?
If you had to come up with the third column entry manually without
formulas, tell us exactly what you would do, what you would look at, to come
up with the True or False. HTH Otto

 

[Paul]

Unfortunately the SUMPRODUCT idea doesnt work, I get a #NUM! returned.
Tried changing trues to 1 and falses to 0 but that didn't help either.

Well, that means that one or other of the arguments is of the wrong type.
Have you correctly altered the ranges A1:A100 and B1:B100 in my formula to
those you are actually using? Have you tried it on a small range of cells?
Are you sure the data are all of the right type?

 

[Hipsway]

The spreadsheet is created from an Access database. All telephone call
are recorded in the database, person who took the call, caller, date
nature of call and whether the query was resolved(complete).

once it is put into excel for analysis I add the columns mentioned, t
find the number of calls recorded by each individual rather than
total for the department. The column I am struggling with wil
hopefully show ie that of the 8 calls taken by John, 6 were completed.

so if I looked along each row I would say" call taken by john
complete - count 1 in johns complete column"

hope this clarifies

 

[Hipsway]

Paul,

I think the reason SUMPRODUCT won't work is that the 2 cells i
question contain text

 

[Paul]

Paul,

I think the reason SUMPRODUCT won't work is that the 2 cells in
question contain text.

OK, so your cells contain the text strings "TRUE" and "FALSE", not the
values TRUE and FALSE? If so, try
=SUMPRODUCT((A1:A100="JOHN")*(B1:B100="TRUE"))

 

[Hipsway]

Still no joy Paul. Apologies if I'm just being a bit thick.

I've attached some of the data so you can see how it works and adde
comments which may help

+----------------------------------------------------------------
| Attachment filename: october calls post.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=357570
+----------------------------------------------------------------

 

[Immanuel]

In O4, use:
=SUM(IF(($B$2:$B$79=M4)*($I$2:$I$79=TRUE),1,0))

In P4, use:
=SUM(IF(($B$2:$B$79=M4)*($I$2:$I$79=FALSE),1,0))

Both need to be array-entered. You can then drag down to the end of your
list.

/i.

 

[Hipsway]

Thanks Immanuel that's worked perfectly

 

[JanetW]

The only problem with using the array formulas is that have no
hard-coded the number of rows of data into your formula. If you ar
doing periodic extracts from your Access database, you will need t
update your formulas each time (or change the row 79 reference in you
fomula to something that will be larger than you'd ever expect to se
in your data set).

An alternative would be to add another column to your data set t
convert your TRUE/FALSE statements to 1s and 0s. If you are using
query to extract your data from Access, set the query properties t
automatically fill down adjacent formulas. Then use the SUMIF functio
in place of the array formulas. The advantage is that SUMIF will accep
whole column references, whereas array formulas require a limited rang
be specified. (They also have the advantage of not being as easil
messed up by forgetting to enter them as arrays!)

In J2 two, enter and fill down:

=IF(I2,1,0)

In O4 enter:

=SUMIF(B:B,M4,J:J)

To get incomplete calls, simply subtract complete from total. So in P
enter:

= N4-O4

Fill down O4 and P4 for your list of names. You now have a somewha
more robust, and simpler, set of formulas which will not requir
updating as the length of you dataset changes. And if you are using
query to get your dataset you don't even have to worry about fillin
the formula in J down to the length of your dataset