Forum Discussion

AFcrta's avatar
AFcrta
Copper Contributor
Apr 09, 2024

Combine SUMIFS function with OR function

Hello to everyone.

I need to create a formula that returns a SUM with different criterias, but I'm not getting it.

 

I have a table

Colum 1 | Column 2 | Column 3

Date       | Price         | Type (1, 2, 3, 4)

 

I need to obtain for a specific date in "Column 1" (I'm having it in another cell) the SUM of the Prices (Column 2) for just Types =1,2,3 (Column 3)

 

I'm doing something like: (the numbers are for explain the columns)

SUMIFS((1)C26:C35;(2)D26:D35;(3)"="&AM3;OR((4)K26:K35="1";K26:K35="2";K26:K35="3"))

 

1:  Column Price

2: Column Date

3: Date I'm having in another cell

4: Column Type where I need to consider the sum of the prices for the types 1, 2 and 3

 

Could you please help me and telling me how I need to do this formula for having it working?

 

Thank you in advance!

  • AFcrta 

    How about

     

    =SUM(SUMIFS(C26:C35; D26:D35; AM3; K26:K35; {1;2;3}))

     

    or

     

    =SUMPRODUCT(C26:C35; (D26:D35=AM3)*((K26:K35=1)+(K26:K35=2)+(K26:K35=3)))

    • AFcrta's avatar
      AFcrta
      Copper Contributor
      Thank you for your reply!.

      The thing is that the field "Type" will grow exponentially, so I will need to specify and create SUMS over more than 50 different types, so that's why I need to find the way to mark wich ones need to be considered for the SUM
      • AFcrta 

        How about

         

        =SUM(SUMIFS(C26:C35; D26:D35; AM3; K26:K35; {1;2;3}))

         

        or

         

        =SUMPRODUCT(C26:C35; (D26:D35=AM3)*((K26:K35=1)+(K26:K35=2)+(K26:K35=3)))