SOLVED

Column Pull Based on General Search

Copper Contributor

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.  

 

Excel.JPG

 

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.   

 

 

4 Replies

Hi Mike

 

A job or SUMIF() oder SUMIFS() or a Pivot Table.

 

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.  

best response confirmed by Mike Perrin (Copper Contributor)
Solution

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  

Just to add to this, if you don't want to edit this formula each time you use it, you can add two cells that include the minimum date and maximum date, then reference those cell sin Sergei's formula above. That way your formula will always be correct and all you need to change is the min and max dates that you want to be reflected in the calculation.
1 best response

Accepted Solutions
best response confirmed by Mike Perrin (Copper Contributor)
Solution

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  

View solution in original post