Formula for counting cells with value 'x' provided Col U = 'y'

J

J.Scargill

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.
 
E

Eduardo

Hi,

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

change values to get the other conbinations
 
T

T. Valko

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)
 
A

Ashish Mathur

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
 
J

J.Scargill

Great, thanks for your help with this and the last few Biff! Greatly
appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top