Prev: setting cell values based on a 3rd cell
Next: Unique Rank with Duplicate Entries
From: J.Scargill on 30 Mar 2010 11:24
Hi guys,
Need some help with a formula please. Have tried myself and know that the
answer is straightforward but going through a mental block!

Col Q Col R
A01 58
A01 62
P02 62

Both columns data starts in cell 14 and runs down to cell 10000.
I need to count the entries in Column Q that = A01 ONLY IF Col R = 62. Then
do the same for Q = A01 ONLY IF R = 58, and so on. The worksheet is a
year-to-date file and will be updated weekly with many entries in both
columns with varying values.

Many thanks.
From: Eduardo on 30 Mar 2010 11:39
Hi,

=SUMPRODUCT((Q1:Q10000="A01")*(R1:R10000=62))

change values to get the other conbinations

"J.Scargill" wrote:

> Hi guys,
> Need some help with a formula please. Have tried myself and know that the
> answer is straightforward but going through a mental block!
>
> Col Q Col R
> A01 58
> A01 62
> P02 62
>
> Both columns data starts in cell 14 and runs down to cell 10000.
> I need to count the entries in Column Q that = A01 ONLY IF Col R = 62. Then
> do the same for Q = A01 ONLY IF R = 58, and so on. The worksheet is a
> year-to-date file and will be updated weekly with many entries in both
> columns with varying values.
>
> Many thanks.
From: T. Valko on 30 Mar 2010 11:44
Try one of these...

Any version of Excel:

=SUMPRODUCT(--(Q14:Q10000="A01"),--(R14:R10000=62))

Excel 2007 or later:

=COUNTIFS(Q14:Q10000,"A01",R14:R10000,62)

--
Biff
Microsoft Excel MVP


"J.Scargill" <JScargill(a)discussions.microsoft.com> wrote in message
news:3163A00C-3A90-4515-8517-E48BEAEF2EA1(a)microsoft.com...
> Hi guys,
> Need some help with a formula please. Have tried myself and know that the
> answer is straightforward but going through a mental block!
>
> Col Q Col R
> A01 58
> A01 62
> P02 62
>
> Both columns data starts in cell 14 and runs down to cell 10000.
> I need to count the entries in Column Q that = A01 ONLY IF Col R = 62.
> Then
> do the same for Q = A01 ONLY IF R = 58, and so on. The worksheet is a
> year-to-date file and will be updated weekly with many entries in both
> columns with varying values.
>
> Many thanks.


From: Ashish Mathur on 30 Mar 2010 21:18
Hi,

Select the range of data (including the header row) and convert it to a
Table/List (Ctrl+L). Now create a pivot table. Drag column Q to the row
area, column R also to the row area and column Q to the data area. This
should get you what you want.

Now when you add any row of data to the range, just right click and refresh
the pivot

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"J.Scargill" <JScargill(a)discussions.microsoft.com> wrote in message
news:3163A00C-3A90-4515-8517-E48BEAEF2EA1(a)microsoft.com...
> Hi guys,
> Need some help with a formula please. Have tried myself and know that the
> answer is straightforward but going through a mental block!
>
> Col Q Col R
> A01 58
> A01 62
> P02 62
>
> Both columns data starts in cell 14 and runs down to cell 10000.
> I need to count the entries in Column Q that = A01 ONLY IF Col R = 62.
> Then
> do the same for Q = A01 ONLY IF R = 58, and so on. The worksheet is a
> year-to-date file and will be updated weekly with many entries in both
> columns with varying values.
>
> Many thanks.

From: J.Scargill on 31 Mar 2010 03:18
Great, thanks for your help with this and the last few Biff! Greatly
appreciated.

"T. Valko" wrote:

> Try one of these...
>
> Any version of Excel:
>
> =SUMPRODUCT(--(Q14:Q10000="A01"),--(R14:R10000=62))
>
> Excel 2007 or later:
>
> =COUNTIFS(Q14:Q10000,"A01",R14:R10000,62)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "J.Scargill" <JScargill(a)discussions.microsoft.com> wrote in message
> news:3163A00C-3A90-4515-8517-E48BEAEF2EA1(a)microsoft.com...
> > Hi guys,
> > Need some help with a formula please. Have tried myself and know that the
> > answer is straightforward but going through a mental block!
> >
> > Col Q Col R
> > A01 58
> > A01 62
> > P02 62
> >
> > Both columns data starts in cell 14 and runs down to cell 10000.
> > I need to count the entries in Column Q that = A01 ONLY IF Col R = 62.
> > Then
> > do the same for Q = A01 ONLY IF R = 58, and so on. The worksheet is a
> > year-to-date file and will be updated weekly with many entries in both
> > columns with varying values.
> >
> > Many thanks.
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: setting cell values based on a 3rd cell
Next: Unique Rank with Duplicate Entries