Hi, I currently have excel 2010 for windows.
I am trying create a a system where excel searches for a "keyword" and finds it and counts the total qty from a 2nd cell, for example:
A-Product Name | B-QTY | C-Ship Method | D CELL | E CELL | etc............
product1 5 UPS
product10 8 FEDEX
product3 9 USPS
product6 2 UPS
product1 7 FEDEX
product2 1 FEDEX
product10 8 USPS
product6 7 UPS
product1 14 UPS
I want to See how many of "Product1" was shipped by FEDEX.
I only want to know the total qty of "Product1" and only the ones that shipped by FEDEX
stevorama,
Assuming headers are in row 1 and the 9 rows of data are rows 2:10, you can use the following formula:
=SUMPRODUCT(--(A2:A10="product1"),--(C2:C10="FEDEX"),B2:B10)
Hope that helps,
~tigeravatar
Since you are using 2010, you can use the countifs function.
http://office.microsoft.com/en-us/ex...010047494.aspx
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
Thank you guys, got it to work, but the results need to appear on a different worksheet, im still having trouble with this:
so far I have this and it brings back 0 as the result, but if I use this same code in the first worksheet the number is correct:
trans is where all the data is contained, worksheet2 is where I want the results to appear.
This code is once again to search for whatever name is in a column(A18) and use that as the search term which will let me know how many of A18(Name) are there for only orders that shipped Expedited
=SUMPRODUCT(--(trans!F2:F10000=A18),--(trans!Z2:Z10000="expedited"),trans!K2:K10000)
I am using this code on worksheet2, trying to pull the sumproduct from the data in "trans"
The only way I am able to get the correct sum total is if I use this code only in the "trans" worksheet since it contains all the data needed
Maybe try:
abousetta=SUMPRODUCT(--(trans!F2:F10000=trans!A18),--(trans!Z2:Z10000="expedited"),trans!K2:K10000)
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
By the way,
Cell F contains the Names of different products I sell
Cell K is the QTY
Cell Z is the shipping method (Standard or Expedited or Overnight)
This code only work if done in the trans sheet, where all of the data is at (Result gives me 2)
=SUMPRODUCT(--(F2:F10000=F151),--(Z2:Z10000="expedited"),K2:K10000)
The code below is what I am trying to use on Worksheet2. (Result gives me 0)
=SUMPRODUCT(--(trans!F2:F10000=F151),--(trans!Z2:Z10000="expedited"),trans!K2:K10000)
F151 is looking on the active sheet not the trans sheet. That why you need to add trans! before it.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Thats correct, I am on Worksheet2 and trying to search for F151, which is on Worksheet2, and look into the Trans Worksheet for the total amount amount of F151 item that is shipped using Expedited Delivery.
Worksheet2 contains all of the product names
Trans contains all of the transactions I downloaded from my website, so there are many duplicate product names which is why i want to search total number of sales for "Product1", that was shipped by expedited delivery
Could you upload a sample workbook?
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Basically here is what I have:
(Worksheet2)
Cell A - Product Name
product1
product2
product3
product4
proxuct5
product6
Cell B - Qty Sold
0
0
0
0
0
0
-------------------------
(Worksheet1"Trans")
Cell F - Products Sold
product2
product 6
product3
product1
product1
product7
product1
Cell Z - Shipping Method Used
expedited
standard
standard
expedited
standard
expedited
standard
Cell K - QTY SOLD
1
1
4
36
11
12
21
7
6
--------------------------------------
So lets say I want to know how many "Product1" sold that was shipped Only by Expedited Mail
The 3 codes below are not working:
=SUMPRODUCT(--(trans!F2:F10000=F151),--(trans!Z2:Z10000="expedited"),trans!K2:K10000)
=SUMPRODUCT(--(trans!F2:F10000=Worksheet2!A2),--(trans!Z2:Z10000="expedited"),trans!K2:K10000)
=SUMPRODUCT(--(trans!F2:F10000="product1"),--(trans!Z2:Z10000="expedited"),trans!K2:K10000)
Remember, the main worksheet I am dealing with is Worksheet2, which is where all of the results from "Trans" will return.
Sure, here is the Example I have attached for you.
Have a look at the attachment. Since you are using a post-2003 version of excel I used sumifs.
I had to make a small modification though to your Sheet2. Your items names were not the same as the tran sheet. For example if in the tran sheet it has [electronics] at the end of the product then you have to have this also or it will not find it. The formulas don't use fuzzy logic and so they will not pick the closest match, they will just ignore them altogether.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Thank you for the file, I actually have 2010 Excel so I would need something that works for my version.
Alot of functions came out after 2003 and that's why you will find people asking what version of excel you use so that they can use functions that are appropriate to your version. Sumifs came out in Excel 2007 and also work in Excel 2010. They don't work in Excel 2003 and that's why you would have to use Sumproduct instead. Long story short... the file works in Excel 2010.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Thank you for your help, it was very professional the way you fixed this quickly for me
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks