SUM Function with Multiple Conditions


G

Glendy

I have two columns, one that lists item number and one
that lists ordered quanities. I have made a function in a
cell that sums up the quantities of all the item numbers
that start off with ME. The formula is listed below:

{=SUM((Left(Sheet1!A2:A5000,2)="ME")*B2:B5000)}

However, I need to add the following information. All the
item numbers that have the beginning start off with ME-008
needs to have their quantities multiplied by 24. How do I
add this new piece of info into the already existing
formula so that the quantities summed include the item
numbers starting off with ME and the items that start off
with ME-008? Any help will be greatly appreciated. Thanks
so much!!!

Glendy
 
Ad

Advertisements

G

Glendy Lin

I have three columns,item number, quantity ordered, and
date. I have made a function in a cell that sums up the
quantities of all the item numbers that start off with ME.
However, anything starting with ME-008 had their quantity
multiplied by 24. So far, the formula is:

=SUMPRODUCT((LEFT(Sheet1!A2:A5000,2)="ME")-(LEFT(Sheet1!
A2:A5000,6)="ME-008"))*(Sheet1!B2:B5000))+SUMPRODUCT((LEFT
(Sheet1!A2:A5000,6)="ME-008")*(Sheet1!B2:B5000))*24

I need to add the following information. I need the
formula to filter out the selection by a date interval
that the user inputs. Let's say the user inputs the data
criteria in Sheet2. Starting date is in cell A2 and ending
date is in cell A3. Therefore, the interval is expressed
as ('Sheet1'!A2:A5000>='Sheet2'!A2)* ('Sheet1'!
A2:A5000<='Sheet2'!A3).
How do I add in the date criteria with the existing
formula I have now. I really appreciate any help. Thanks
so much!!!
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top