You are here:

Excel/Count the number of columns based on first entry in multiple rows

Advertisement


Question
QUESTION: Hi,
I have six columns, A thru F.
Some of the column A contains pre-populated value "Y" (about 20 columns out of 50).
From B onwards I have values 1, 2, or 3 across alternate columns such as D or F.  These values not necessarily start from column B.  Some times you will see the first entry starting in D or F.

The business rule is as follows:

Count # of Y's where Column value is 1 in either column B or D or F (look for the first instance of 1 horizontally).  

Any help would be greatly appreciated.  Thanks!

Best Regards,
Thej

ANSWER: An interesting question, in that it is a sumifs with conditionals - I think that the following array formula would do it

=SUM(IF(A1:A6="Y",IF(B1:B6=1,1,IF(D1:D6=1,1,IF(F1:F6=1,1,0)))))

replace rows 1:6 with the values you actually need, and as this is an array formula it would need entering wtih ctrl shift enter - I hope that this makes sense but do let me know if I can help further.

---------- FOLLOW-UP ----------

QUESTION: First of all, thanks for the quick reply!

I did try the formula but it is adding all the columns with values containing 2.  In my case, it is showing a value of 2 (it is supposed to give me a value of 1).  So I changed the formula to the following:

=COUNT(IF(K4:K46="Y",IF(M4:M46=2,2,IF(O4:O46=2,2,IF(Q4:Q46=2,2,0)))))

resulting in a value of 6 in which case the total number of rows containing Y and not giving me the expected value of 1.

I think the formula is counting all the instances of value 2 across all the columns M, O, and Q.  I want the number of Y's to be counted only once for the value of 2 and 3 and so on.  Thnaks again!

regards,
Thej

Answer
the follow up question is a little different to the original - the original business rule was

Count # of Y's where Column value is 1 in either column B or D or F (look for the first instance of 1 horizontally).  

which my formula handled - as follows

=SUM(IF(A1:A6="Y",IF(B1:B6=1,1,IF(D1:D6=1,1,IF(F1:F6=1,1,0)))))

means IF the value of a1:a6 is a Y, then (and only then) check the value of B1:B6 - if that is a 1, you are going to COUNT it so store a 1 otherwise you are not going to count it so store a zero (these two values remain as 1 and zero regardless of the test you do against B1:b6 as they are used for counting purposes - I've had to use SUM to get the value as I cannot use count).  If you didn't get a value you wanted to store in column B, repeat the test for columns D and F as necessary.

SO if you wanted to count Y with 2 against it the formula would be

=SUM(IF(A1:A6="Y",IF(B1:B6=2,1,IF(D1:D6=2,1,IF(F1:F6=2,1,0)))))  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.