Hello all I have a macro that copies all the sheets from two workbooks into a new workbook. That is all good and working. Each sheet contains x-number of rows with items in them. Each of those items has a serial number attached to it. Some of those items are duplets and I want to count the number of occurrences across all of the worksheets in that combined workbook and display it in the column next to the item being tested.

The way I have done it at the moment is by using a code that Counts the number of worksheets and then selects a given case corresponding to that number of worksheets.

When the case is selected, it puts in a formula like this in all the U-colomns in each worksheet:

=COUNTIF('Sheet1'!T:T;T1)+COUNTIF('Sheet2'!T:T;T1)+COUNTIF('Sheet3'!T:T;T1)

This gives me the correct number of occurrences, but it is not very easy to maintaine. As you all surely can see, the above formula that gets inserted is if case 3 is selected. The problem is that I have up to 20 cases, where I have 20 countif-functions added together.

Any suggestions to how to make this simpler? I know there must be a way. It doesnt have to put in a formula at all in that range("T:T"), the result of the above formula is all that I need.

Thanks in advance

/Jacob

message edited by JacobJ

Try this, =SUMPRODUCT(COUNTIF(INDIRECT("'"&"Sheet"&{1,2,3}&"'!T:T"),T1))

Only tested it out a bit, so no guarantees.

MIKE

If all you need is the result of the COUNTIF, how about something like the following suggestion? I don't know if this will fit your exact needs, but it's the loop concept that's important. Modify as required.

Sub Count_TT() For shtNum = 1 To Sheets.Count myCount = _ myCount + WorksheetFunction.CountIf(Sheets(shtNum).Range("T:T"), Range("T1")) Next 'Substitute your use of the answer here MsgBox myCount End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks for the answers. The solution you give derbydad03 is sort of what I need. But it only gives the answer for the value in T1 and not the Whole range Down in column T. I think I need a loop within that loop, that does this counting for every cell in that list of items in column T on each sheet. But I am a bit stuck after I have calculated the number of times needed to run the loop:

For shtNum = 1 To Sheets.Count For singlecell = 1 To Sheets(shtNum).Range("B" & Rows.Count).End(xlUp).Row myCount = _ myCount + WorksheetFunction.CountIf(Sheets(shtNum).Range("T:T"), Range("T1")) Sheets(shtNum).Range("U1").Value = myCount Next singlecell Next shtNumI think that something has to be done in the range("T1") and range("U1") but not sure how to loop those ranges..

First, a posting tip related to posting VBA code in this forum. You may have noticed that I edited your post so that the VBA code fits within the borders of the post frame. If the code contained within the pre tags extends beyond the right hand border, it causes the rest of the text (the non-code text) to also extend beyond the right hand border. That means that readers have to use the horizontal scroll bar to move the frame back and forth in order to read what was posted. (It's nothing that you are doing "wrong", it's just a limitation of the text editor in this forum). By using the Line Continuation character within the code, you can keep the code within the frame but also be able to Paste the code right into the VBA editor. OK, that said, I'm a little confused by your requirements. Please keep in mind that we can't see your workbook from where we're sitting so, so we're at a bit of a disadvantage.

I

thinkthat you want to COUNTIF T1, then T2, then T3, placing those values in U1, then U2 then U3, respectively. I alsothinkthat you want to do this on each sheet individually. In other words, you are not aggregating Column T values across all sheets.If that's the case, then try the code suggested below. If that's

notthe case, then you may need to post some example data, along with the expected results based on that data so we can fully understand what you are trying to do.Sub Count_TT() 'Loop through all sheets For shtNum = 1 To Sheets.Count 'Loop through Column T based on length of Column B For singlecell = 1 To Sheets(shtNum).Range("B" & Rows.Count).End(xlUp).Row 'COUNTIF individual values within Column T of current sheet With Sheets(shtNum) myCount = _ WorksheetFunction.CountIf(.Range("T:T"), .Range("T" & singlecell)) 'Place result in corresponding Row in Column U of current sheet .Range("U" & singlecell).Value = myCount End With 'Reset mycount variable myCount = 0 Next singlecell Next shtNum End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hello again. I am sorry to say, that this isnt the case. My data is like this. The data I can export carries "old" data with it. That Means, that if some data was present the first time I exported the data, it will also be present the next time, plus the new items that has been added.

I am only interested in those new data, therefore I am at the moment doing the following:

When I export my data a counting column gets placed in every data set. This simply counts the number of times a given item is present.

I then merge two (and sometimes more) sets of data. Each set of data is put into individual sheets in the same workbook. Now my idea is to count each item accross both(all) sheets and see how many a given item there is present now. If the counting results in twice as many numbers of the given item, that means there has been no addition to this item. If the number is more than twice as much, then items has been added.

Therefore counting how many of a given item there is across all sheets is needed and the results has to be displayed just as it is done in the code you have posted above.

I'm not sure if you are saying that the code I offered works for you or not. First you say:

"I am sorry to say, that this isn't the case."Then you say:

"Therefore counting how many of a given item there is across all sheets is needed and the results has to be displayed just as it is done in the code you have posted above."The first part sounds like my solution doesn't work, but the second part makes it sound like it does work.

At this point I'm confused as to whether you need more help or not. If you do, you'll have to tell me what my code doesn't do that you need it to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hello Again Your code Works very nicely for counting the items on each seperate sheet, but the problem is, that I need it to Count across multiple sheets, not just add a countif to Count only on one sheet. For the following examples I have modified the "ranges" in your code as followed.

Sub Count_TT() 'Loop through all sheets For shtNum = 1 To Sheets.Count 'Loop through Column T based on length of Column A For singlecell = 1 To Sheets(shtNum).Range("A" & Rows.Count).End(xlUp).Row 'COUNTIF individual values within Column B of current sheet With Sheets(shtNum) myCount = _ WorksheetFunction.CountIf(.Range("B:B"), .Range("B" & singlecell)) 'Place result in corresponding Row in Column C of current sheet .Range("C" & singlecell).Value = myCount End With 'Reset mycount variable myCount = 0 Next singlecell Next shtNum End Sub

The results are displayed in column C, what I need is in column D:Sheet 1 A B C D 1 1 a 2 4 2 2 d 1 2 3 3 e 1 1 4 4 c 1 3 5 5 b 2 4 6 6 a 2 4 7 7 b 2 4Sheet 2 A B C D 1 1 a 2 4 2 2 d 1 2 3 3 b 2 4 4 4 a 2 4 5 5 b 2 4 6 6 c 2 3 7 7 c 2 3The results in column D is achieved by these two formulas filled down:

Sheet1: =COUNTIF(B:B,

B1)+TÆL.HVIS('sheet2'!B:B,B1)

Sheet2: =COUNTIF(B:B,B1)+TÆL.HVIS('sheet1'!B:B,B1)As you can see, the item "a" is present 2 times on sheet1 and 2 times on sheet2, so it is present 4 times when counted across both sheets.

I hope that is a more clear description.

Sub Count_TT() 'Loop through all sheets to Count Items and place values For destSht = 1 To Sheets.Count 'Determine number of items to count on current sheet lstRw = Sheets(destSht).Range("A" & Rows.Count).End(xlUp).Row 'Loop through items on current sheet For myItem = 1 To lstRw 'Loop through all sheets, counting current item For srcSht = 1 To Sheets.Count myCount = myCount + _ WorksheetFunction.CountIf(Sheets(srcSht).Range("B:B"), _ Sheets(destSht).Range("B" & myItem)) Next 'Place Count on current sheet Sheets(destSht).Range("C" & myItem) = myCount 'Reset counter myCount = 0 'Count next item on current sheet Next myItem 'Move on to next sheet Next destSht End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll