Running count of ref codes across all sheets of monthly data

**Mohammad Munawar** · 08-30-2020, 03:27 AM

Hi,

required a formula to identifying about how many times the particular refer code repeated in a full month of date with different sheets.

Here is the formula which i used to count in a single sheet ref codes =B6&":"&COUNTIF($B$6:$B6,B6)

need a formula to adding a previous sheet ref codes count for a running count.

Thanks,

**XOR LX** · 08-30-2020, 04:48 AM

Hi,

In A4 of each sheet:

=B4&":"&SUM(IF(SEQUENCE(ROWS(B$4:B$12)*SHEETS(Sheet1:Sheet3!A1))<=SHEET()*ROWS(B$4:B$12),N(FILTERXML("<a>"&TEXTJOIN("",1,Sheet1:Sheet3!B$4:B$12)&"</a>","//b")=B4)))

Copy down as required.

Amend the part Sheet1:Sheet3 as required (such that it comprises the leftmost and rightmost worksheets in your workbook).

Regards

**Mohammad Munawar** · 08-30-2020, 06:17 AM

Hi,

Thanks for you reply to my query,

I pasted the formula in a worksheet as you advised, may be some issue in calculation,there is Cell B4 code is A1, actually it is to be count after inserting the formula

is A1:1 now its calculating A1:2

for your reference i enclosed sample.

Thanks once again,

**XOR LX** · 08-30-2020, 06:27 AM

Not sure I understand what's wrong with the current results my formula returns.

The formula in cell A4 of Sheet1 returns "A1:2" because there are:

2 entries of "A1" in column B of Sheet1

The formula in cell A4 of Sheet2 returns "A1:4" because there are:

2 entries of "A1" in column B of Sheet1 and
2 entries of "A1" in column B of Sheet2

The formula in cell A4 of Sheet3 returns "A1:6" because there are:

2 entries of "A1" in column B of Sheet1 and
2 entries of "A1" in column B of Sheet2 and
2 entries of "A1" in column B of Sheet3

i.e. a running count of code "A1" in column B for all sheets up to and including the active sheet.

Regards

**Mohammad Munawar** · 08-30-2020, 06:53 AM

yes you're correct,

it is adding the total entries of the particular entered code in column B affecting to all sheets, sheet by sheet ,How ever it is use full, Running count of column B across the all sheets much better use full than this.

Thanks,

**XOR LX** · 08-30-2020, 07:43 AM

Ah, that case is much simpler!

=SUMPRODUCT(N(FILTERXML("<a>"&TEXTJOIN("",1,Sheet1:Sheet3!B$4:B$12)&"</a>","//b")=B4))

Regards

**Mohammad Munawar** · 08-30-2020, 08:03 AM

Thank you so much,

solved, very much Considerable solution.

**XOR LX** · 08-30-2020, 08:05 AM

You're welcome!

Regards

Running count of ref codes across all sheets of monthly data

LinkBack

Thread Tools

Rate This Thread

Display

Running count of ref codes across all sheets of monthly data

Re: Running count of ref codes across all sheets of monthly data

Re: Running count of ref codes across all sheets of monthly data

Re: Running count of ref codes across all sheets of monthly data

Re: Running count of ref codes across all sheets of monthly data

Re: Running count of ref codes across all sheets of monthly data

Re: Running count of ref codes across all sheets of monthly data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count Monthly filtered data

[SOLVED] Gather data from all sheets using codes

fixed cost monthly that may change used in running balance not changing past data

How to use the countif funtion to count a codes that contain cells with muliple codes.

Keep a running count of Overtime on a Monthly basis

Running Monthly Totals on Multiple Sheets

count codes in a column w. several codes in a same cell

Bookmarks

Bookmarks

Posting Permissions