Need an =if statement

[DFlester]

What I need is to be able to add the units of each product seperately
in the Summary sheet, and get percentages. Essentially I need to be
able to tell the Summary sheet the following: If any cell Column D in
the Order List Sheet = Potatos (or cookies, coffee, meat..etc), then
add the number in Column C in the corresponding cell to the "running
total". Note: Column D cells are a dropdown list (of various products)
tied to the Summary page

I have a sample spreadsheet I can show if it would help.

ANY HELP would be GREATLY APPRECIATED.....as I have been working on
this for over a week (off and on)

 

[JLatham]

Perhaps this 'hint' will give you a start, this assumes values in rows 1
through 4, change as required. It gives the total of all "Potato" entries in
the range (word in D, quantity in C)
=SUMPRODUCT(--(D14="Potato"),C1:C4)

there is almost always some help to be had at
HelpFrom @ jlathamsite.com
remove those spaces - if you really want help.

 

[boosbrde]

Ok,
Here's what I tried (one of various) =SUMPRODUCT(=if'Order List'!
D250=potatos),(D250="Potato"),(C2:C50)

I think I must have misunderstood the formula you are showing me,
because it didn't work. It says the formula I typed contains an
error. I have to admit, I'm not very experienced with formulas, just
basic stuff. My mind tells me the formula should (more or less) say:

If any cell in column D on the Order List equals potatos add it here
in this cell in the summary sheet, and if it doesn't equal it, just
ignore it.

and, that I should be able to use the same formula (changing names of
course) for the other products. The problem is, I really don't know
how to write it in language excel will understand.....

 

[boosbrde]

Sorry, I am at work, and got interrupted when I posted this...just
realized I forgot an important element, it should say:

If any cell in column D on the Order List equals potatos add the
number in the corresponding cell in column D here
in this cell in the summary sheet, and if it doesn't equal it, just
ignore it.

 

[Elkar]

Try this:

I'm going to assume that cell A2 of your Summary Sheet contains: Potatoes
In cell B2, enter the formula:

=SUMPRODUCT(--('Order List'!D250=A2),C2:C50)

Then, assuming cell A3 of your Summary Sheet contains: Cookies
Copy the formula in cell B2 down to B3, and you'll have your results for
Cookies. Continue this with each value in Column A that you want to
summarize.

If your list of products starts somewhere other than A2, then adjust the
formula accordingly.

HTH,
Elkar

 

[JLatham]

The system was unresponsive earlier, I sent you the following comments via
email direct along with a workbook with example formulas. I'll repeat the
body of the email here. Feel free to respond to the email if you need more
help. As Elkar has shown, you can also reference a cell with the word to be
matched in it instead of the actual word. The email body:

You’re close with your formula, but you need to keep things separated.
A few things wrong with your formula as shown in your 2nd post at the site:
=SUMPRODUCT(=if'Order List'!
D250=potatos),(D250="Potato"),(C2:C50)

The main problem is that you can’t have the second = within the formula as
you have it., plus you needed to enclose the word potatos within quotes
(although that won’t fix it all). Also your matching of ( and ) is incorrect.

Assuming that your list of products and quantity is on sheet ‘Order List’,
then the formula to return the total of orders for Potato would be
=SUMPRODUCT(--(‘Order List’!D250=â€Potatoâ€),’Order List’!C2:C50)

What happens here is that the first part of the formula, --(‘Order
List’!D250=â€Potatoâ€) Looks at each row from 2 to 50, column D to see if it
contains the word Potato (if it is actually Potatos, then change that in the
formula). It returns a true/false indication for each row, and true is
returned as -1, false is returned as 0. The -- in front of the test
converts -1 to a positive 1 when the result is true. The result of that test
(either 0 or 1) is used to multiply the value in the same row on that sheet,
so when Potato is on a row, the formula is same as 1 * quantity, and if the
word isn't Potato, it comes out as 0 * quantity. Each row's result is
tracked internally and they are all added together to give a total for all
rows with Potato in column D.

If you wanted to include several different products in your total, you can
simply add more instances of the SUMPRODUCT() within the total cell, as (all
would be written on a single line in the cell just as with any worksheet
formula)...

= SUMPRODUCT(--(‘Order List’!D250=â€Potatoâ€),’Order List’!C2:C50) +
SUMPRODUCT(--(‘Order List’!D250=â€Tomatoâ€),’Order List’!C2:C50) +
SUMPRODUCT(--(‘Order List’!D250=â€Carrotâ€),’Order List’!C2:C50)