Forum Discussion
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
- Detlef_LewinSilver Contributor
Hi Mike
A job or SUMIF() oder SUMIFS() or a Pivot Table.
- Mike PerrinCopper 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.
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