COUNTIF with adjacent columns

[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!

 

[Stefi]

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:

 

[Custard Tart]

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

Fill it to the right and down!
Adjust ranges!

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.

 

[Stefi]

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.

In a range at the bottom of your task list, in my example A2022, in
column A list all tasks (CL, BL, AC etc.), in B2020 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

 

[Ashish Mathur]

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