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

  • Thread starter Thread starter J.Scargill
  • Start date Start date
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.
 
Hi,

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

change values to get the other conbinations
 
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)
 
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
 
Great, thanks for your help with this and the last few Biff! Greatly
appreciated.
 
You must log in or register to reply here.

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

Similar Threads

Positioning Numeric Values Resulting from 6 Column Array Formula 2
VLOOKUP Function returning multiple values in a separate table 8
function returning SUM of 2 adjacent cols in 2 separate cells 7
Formula For Counting Records 5
Match with 3 criteria 3
Count Duplicates in text values 6
Formula assistance required 3
Help! Forgot formula for auto copy of cell value... 6

Back
Top