Countif on two columns in a sheet

[Guest]

Hello fellow Excellians,

On my last question I got a perfect answer. I wanted to know how many times
a particular value could be found in a certain column in a certain tab.

Now I want to know how I can use the same by selecting two values. I have a
sheet with the following values.

Date In (Column A)
Number (Column B)
Solved by (Column C)
Flow (Column D)
Cause (Column E)
etc, etc

Now I want to know how many times the person (in Column C) has solved a
particalur issue concerning a Flow (Column D). With selecting only one value
(for the situation how many times does a certain person solve an issue you
will use the following statement =COUNTIF($F$2:$F$12336;c1) ). But how do you
include an and in this statement as you make a selection on two different
values.

I will be very gratefull for your answer.

 

[Bob Phillips]

=SUMPRODUCT(--($C$2:$C$12336=C1),--($D$2:$D$12336=D1))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Dear Bob Phillips,

Forgive me if I am wrong but the below would take the number of times a
certain values is found in column C and add that to a number of a certain
value found in D. And this is not what I am looking for.

Maybe simplyfying might help.

A B C D
01-11-2005 1 Jaydubs Flow 1
01-11-2005 2 Bob Flow 2
01-11-2005 3 Bob Flow 1
01-11-2005 4 jaydubs Flow 3
02-11-2005 5 John Flow 2
02-11-2005 6 John Flow 1
02-11-2005 7 Bob Flow 1
02-11-2005 8 Bob Flow 1

What I am looking for is a formula which would give me the numbers as follows:

Flow 1 Bob 3 (times)
Flow 1 Jaydubs 1 (times)
Flow 1 John 1 (times)
Flow 2 Bob 1 (times)
Flow 2 John 1 (times)
Flow 3 Jaydubs 1 (times)

Unfortunately your formula does not seem to help me. Sorry !!

 

[Bob Phillips]

Try it and see if you are correct (hint - you are not).

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hint Taken !!

Thanx 4 the help !!

 

[Guest]

Hey Great, Pivots, never used them, but sounds right. I will have a go.

Thanx mate !!

 

[Roger Govier]

Hi

I think Bob had a small typo in his formula, which would have returned 0 for
the first cell. It followed from the formula you posted, where you were
looking at a data range from row 2, but were comparing against the header
values in row 1, not data values in row 2.

With the formula in say E1, it should be
=SUMPRODUCT(--($C$1:$C$12336=C1),--($D$1:$D$12336=D1))

When then copied down column E, then the amended formula from Bob will
return your results, but maybe not quite in a form you would like to see.

Perhaps if you were to enter in cells H1:J1, Bob, Jaydubs, John and in cells
G2:G4 you entered Flow 1, Flow 2, Flow 3 then the formula below entered in H2
=SUMPRODUCT(--($C$1:$C$12336=H$1),--($D$1:$D$12336=$G2))
and copied across thro' cells I2:J2, and H2:J2 copied down thro' H3:H4 will
produce a matrix of desired results.

Alternatively, your data is highly suitable for analysis with a Pivot Table
Mark the range of your data.
Data>Pivot Table>Next>Layout
Drag the Solved by field to the Column area
Drag the Flow field to the Row area
Drag the Solved field also to the Data area, double click the field and
ensure it is set to Count.
Press Finish

For more help on Pivot Tables see
http://peltiertech.com/Excel/Pivots/pivotstart.htm
and
http://www.contextures.com/xlPivot02.html


Regards

Roger Govier