Formula for counting cells with value 'x' provided Col U = 'y'
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. |
Formula for counting cells with value 'x' provided Col U = 'y'
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. |
Formula for counting cells with value 'x' provided Col U = 'y'
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" wrote in message ... 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. |
Formula for counting cells with value 'x' provided Col U = 'y'
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" wrote in message ... 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. |
Formula for counting cells with value 'x' provided Col U = 'y'
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" wrote in message ... 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. . |
Formula for counting cells with value 'x' provided Col U = 'y'
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "J.Scargill" wrote in message ... 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" wrote in message ... 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. . |
All times are GMT +1. The time now is 11:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com