Forum Discussion

Zsolteemoltee's avatar
Zsolteemoltee
Copper Contributor
Nov 02, 2022
Solved

column-header based data counting

Hi,

 

I have SRC and DST columns, both have DATEs as headers.

DTS columns ROWs are unique with specific text to be collected and represented. 

 

SCR columns are not in row so there are other columns without DATE header.

 

I would like to count specific text in each SRC columns and add result to the defined row of DST column.

 

I would like to collect from several SRC sheets from the same DATE.

SUM(COUNTIF('Eszter Pasztori'!C:C,$D43),COUNTIF('Réka Valaczkai'!D:D,$D43))

 

 

 

ATM I can collect the text count based on the follwing:

SUM(COUNTIF('SRC_SHEET1'!C:C,'DST_SHEET'!$D43),COUNTIF('SRC_SHEET2'!D:D,DST_SHEET'!$D43))

SUM(COUNTIF('SRC_SHEET1'!C:C,DST_SHEET'!$D44),COUNTIF('SRC_SHEET2'!D:D,DST_SHEET'!$D44))

SUM(COUNTIF('SRC_SHEET1'!C:C,DST_SHEET'!$D45),COUNTIF('SRC_SHEET2'!D:D,DST_SHEET'!$D45))

 

Text search criteria: Column D in DST sheet.

 

SRC Column headers: 2022-11-10, 2022-11-11, etc... 

DST Column headers: 2022-11-10, 2022-11-11, etc... 

 

I would like to decide which column I am counting text for based on the DATE value in all columns headers (the same DATE in DST and SRC need to be the result column)

 

 

 

 

excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Nov 06, 2022

    Zsolteemoltee 

    Hi, I think you can solve your problem with MATCH() and OFFSET(). I have created an example. I hope you can use it for your task.

     

     

    You can probably only get a better answer if your problem is described in more detail. Perhaps you would like to upload an example file. Then it will certainly be easier to help you.

2 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Nov 06, 2022

    Zsolteemoltee 

    Hi, I think you can solve your problem with MATCH() and OFFSET(). I have created an example. I hope you can use it for your task.

     

     

    You can probably only get a better answer if your problem is described in more detail. Perhaps you would like to upload an example file. Then it will certainly be easier to help you.

    • Zsolteemoltee's avatar
      Zsolteemoltee
      Copper Contributor
      Nov 06, 2022
      Dear dscheikey,

      Here is another, more detailed post, can you open it?
      https://answers.microsoft.com/en-us/msoffice/forum/all/excel-vlookup-hlookup-and-countif-alltogether/61cd338f-cb69-48d0-9c52-90af67d256e6

      I got another solution as follows:
      "COUNTIFS(INDEX(INDIRECT("'"&$A$2&"'!$A$2:$MMM$300"),0,MATCH(W$1,INDIRECT("'"&$A$2&"'!$A$1:$MMM$1"),0)),$E18)"

      I will check yours as well.
      A really appreciate your hints and support!

      Cheers,
      Zs