Hi,
I want to use a countif function, counting values in column 1-9, The next countif function needs to count values in column 10-18 etc..
As i have 4000 columns to count and don want to key in 450 times the same countif statement (except different columns to count) I wonder if there is an easy way to automate the countif statement so i can key it in once and copy and paste it 449 times.
Hope someone has an easy solution for me?
Paste it how, horizontally or vertically?
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
paste horizontally
Cell A1 to reflect column A-I
Cell B1 to reflect column J-R etc..
"Cell A1 to reflect column A-I"
You'll get circular references if you mean A1:I(row number)
What row ranges are we talking about?
You'll get a quicker solution if you supply all the information rather than me having to ask one question at a time.
First apply the following formula to A1:Copy out to the end of the worksheet by pressing Ctrl+Shift+right arrow and selecting fill right.
Please Login or Register to view this content.
While the cells in row one are still selected:
Put the following formula in Conditional Formatting:Select Format>Font>Color>WhitePlease Login or Register to view this content.
In the attached file you can see how the formulas and formatting rule are applied.
Countifs Multiple Columns.xlsx
Let me know if you haven any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Above solution is not the solution I am looking for.
I have 1 worksheet (1) with 4000 columns of data.
In a separate worksheet (2) I want to put a countif statement in cell A1 counting data in the first 9 columns of worksheet (1). In cell B1 I want to count the data of column 10-18 of worksheet (1). Next in cell C1 of worksheet (2) I want to count the data of column 19-27 of worksheet (1).
As I will need approx 450 formulas in worksheet (2) to cover all data, 4000 columns of worksheet (1) I would like to state the countif function in such way that I can copy it and paste it 449 times into worksheet (2).
have you tried this?
Put this in A1 worksheet 2
=COUNTIF(Sheet1!A1:I1,"your criteria")
then copy it to next cell, B1,C1 etc.
Yes tried that, but that does not work as it only shifts formula from A-I to B-J.
Pesky little details are useful to know from the outset.Originally Posted by Raspia
This solution will require a helper row (which can be hidden) of cells to be placed somewhere on sheet2, I placed it in row 3. The formula that populates the first row is:Countifs Multiple Columns.xlsxPlease Login or Register to view this content.
Let me know if you have any questions.
You didn't specify what the actual criteria is that you want to count, so I just put "criteria"
Try
=COUNTIF(OFFSET($A$2:$I$100,0,(COLUMNS($A:A)-1)*9),"criteria")
Don't change the $A:A reference, it is not related to your data.
Thanks this worked for me!
You're welcome, thank you for the feedback and the reputation. Let me say that Jonmo1's formula is superior to the solution I provided based on not needing a helper row. Since the formula is placed on Sheet2 it will need a slight modification as in:I am assuming the criteria that you are looking for is any cell that contains a value. If you are satisfied with the solutions provided please take a moment to mark the thread 'Solved' using the thread tools link above your first post. Hope that you have a good day.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks