Forum Discussion

Mojibure's avatar
Mojibure
Copper Contributor
Jan 04, 2023

Formula Correction

Hey Everyone,

I am trying to apply one formula but it's giving me no results.

Can anyone please correct this formula?

Here is the formula

 

=SUMIF(CONCATENATE("E:E",MATCH("ABC",A:A,0)),"T90",B4:B12)

 

This part "CONCATENATE("E:E",MATCH("ABC",A:A,0)" separately giving me range which I wanted which is a range for sumif but when I am trying to insert it in sumif it's giving no output.

 

Thanks in advance

Rehman

excel
Formulas and Functions

6 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 05, 2023

    Mojibure 

    The two ranges in SUMIF should have the same size, Since the sum range B4:B12 has 9 cells, the criteria range should have 9 cells too.

    CONCATENATE("E:E",MATCH("ABC",A:A,0)) returns a string such as "E:E11", not a range...

    • Mojibure's avatar
      Mojibure
      Copper Contributor
      Jan 05, 2023
      Hi,
      You are right but this is what I am trying to bring.
      I have explained the situation in comments can you please check and it would be great if there is you can help.
  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Jan 05, 2023

    Mojibure 

    Try using it with indirect function on your concatenated word. 
    =SUMIF(INDIRECT(CONCATENATE("E1:E",MATCH("ABC",A:A,0))),"T90",B1:B12)

    • Mojibure's avatar
      Mojibure
      Copper Contributor
      Jan 05, 2023

      Rodrigo_ 

      Thank for the reply.

      I have attached the snip for scanerio.

      What I want is sum of column B on the basis of column C for each lebel of column A like for 123000, 87900 & 99800.

      For info: here I have created only one pivot table but we have multiple pivot table like this below.

       

      If you can frame any other formula that would be helpful or great if we can fix this one.

       

Resources