COUNTIF with adjacent columns

  • Thread starter Thread starter Custard Tart
  • Start date Start date
C

Custard Tart

I have a spreadsheet which has a list of tasks in one column and
directly next to it whether that task is complete/not complete/
rescheduled.

For example in cell B2 the task is CL and in column C2 the task is
complete:

B C
2 CL Complete

I would like to add a tally box at the bottom for all CL tasks which
are complete, then another for not complete and another for
rescheduled. At the moment, I can only get it to COUNTIF on either the
CL or the complete value but not both together.

Any help greatly appreciated!
 
Try this:
A B C D
20 complete not complete rescheduled
21 CL 2 2 1
22 BN 1 2 2

Formula in B21:

=SUMPRODUCT(--($A$2:$A$11=$A21),--($B$2:$B$11=B$20))

Fill it to the right and down!
Adjust ranges!


--
Regards!
Stefi



„Custard Tart†ezt írta:
 
=SUMPRODUCT(--($A$2:$A$11=$A21),--($B$2:$B$11=B$20))

Fill it to the right and down!
Adjust ranges!
Click to expand...

Thanks for your quick reply, Stefi, but I'm not sure what I'm supposed
to be doing with that formula! I hope I've explained it well.

Tile of Column / Title of Column
CL / Complete
BL / Not Complete

Etc. with various different tasks (CL, BL, AC etc.) and complete/not
complete/rescheduled.

Thanks for any help.
 
Thanks for your quick reply, Stefi, but I'm not sure what I'm supposed
to be doing with that formula! I hope I've explained it well.

Tile of Column / Title of Column
CL / Complete
BL / Not Complete

Etc. with various different tasks (CL, BL, AC etc.) and complete/not
complete/rescheduled.

Thanks for any help.
Click to expand...

In a range at the bottom of your task list, in my example A20:D22, in
column A list all tasks (CL, BL, AC etc.), in B20:D20 list statuses
(complete/not complete/rescheduled), and copy the formula in B21, the
fill it to the right to D21 then down to D22 (of course in the real
table to Dlast_task).
In cell B21 you'll see the number of completed CL tasks, in C21 the
number of not completed CL tasks, etc.

Hope you meant this.

Regards,
Stefi
 
Hi,

Create a pivot table. Drag column B to the row area, column C to the column
area and column B(again) to the data area

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top