Forum Discussion

Mike Perrin's avatar
Mike Perrin
Copper Contributor
May 09, 2017

Column Pull Based on General Search

Good Morning,

      I am having trouble trying to get a column to generate totals while referencing the column next to it.  I've attached photos to try to help better show the situation as my explanation can/probably is confusing.  

 

So column H is a column in which we put the item itself, for this example... cookies.

Column I is the amount.  

 

 

In column A (not pictured) we have a general list of cookies, pasties, donuts, etc. that we have.  Column B (not pictured) we have total amount from that one particular order.  

 

I want to have Column 'I' to be able to search and total all the numbers from Column B, but only picking out 'Cookies' from Colum A.  I would continue this formula for all the other items we sell.  Currently I am having to spend an hour every few days going line by line and adding the totals myself.

 

Any and all help is always appreciated.   

 

 

  • Hi Mike,

     

    As Detlef mentioned better to use SUMIFS, you have to have it if your version of Excel is 2007 or later.

     

    In your case it will looke like

    =SUMIFS(B:B, D:D, "Cookies", C:C, ">=" & <min date>, C:C, "<=" & <max date>)

    if your dates are in C:C and you will use actual references on min and max dates  

    • Mike Perrin's avatar
      Mike Perrin
      Copper Contributor

      Hi Detlef,

           So I wrote =sum(if($D$2:$D$5000="Cookies",$E$2:$E$5000,0))      then locked it, and it pulled like I needed, now the next step that I just realized, that I forgot to mention, is how to get it also pull from certain date ranges.  

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        Hi Mike,

         

        As Detlef mentioned better to use SUMIFS, you have to have it if your version of Excel is 2007 or later.

         

        In your case it will looke like

        =SUMIFS(B:B, D:D, "Cookies", C:C, ">=" & <min date>, C:C, "<=" & <max date>)

        if your dates are in C:C and you will use actual references on min and max dates  

Resources