This is the workbook:
It is in the attachment I uploaded.
I need to find the total prices for Cans that have Filling and which cost at least 60000.
I tried the formula below but i am getting an error msg.
Please Login or Register to view this content.
Last edited by excel girl; 03-22-2014 at 05:43 PM.
Hi and welcome to the forum
I did not open your file (yet), but try this....
=SUMIFS(E12:E63;H11:H63;"Can";H12:H63;"Filling";E12:D6,">= 60000")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi, i made an error with my formula so i just changed it. I tried using the commas an it doesn't work. The semi colons are accepted for other functions on my laptop in excel 2013.
=SUMIFS(E12:E63;H11:H63;"Can";H12:H63;"Filling";E12:E63;">= 60000")
I have tried using this but still it does not work.
Last edited by excel girl; 03-22-2014 at 05:48 PM.
OK I looked at your file. It bears no resemblance to what you have in that formula, no columns contain either "Can" or "filing", and E (the sum range) is phone numbers. Your formula ranges are 11:63, but your data starts in 2?
Also, you atr testing for 2 things in the same column in your sumifs (sorry, didnt notice that before), That wot work.
I suggest you check your file, then upload the file that is specific to the formula you posted (or give the "corrected" formula for that file?)
Sorry about that error, here is the correct file. I uploaded the wrong one
Refer to sheet 1 of the workbook.
Please check carfully what you upload.
=SUMIFS(E12:E63;H11:H63;"Can";H12:H63;"Filling";E12:D6,">= 60000")
Column H is empty, and column E is a size
What exactly are you trying to do there?
ok, here it is.
Perhaps you missed this part?
What exactly are you trying to do there?
I need to find the total prices for Cans that have Filling and which cost at least 60000.
Is anyone available to assist?
If you mean you want to sum only those rows where QTY in stock * Retail Prrice is at least 60 000, then the answer will be 0. You have a max of QTY*Price of 1209.08, and a grand total of only 25604
Thank you, i got that answer also. How do i go about finding the>> arithmetic mean of revenues earned ? I am required to use an array formula
This sounds like a homework assignemt?
I am practicing questions given in class. I have an upcoming practical exam.
I know you said an array formula, but here is a sumproduct version. I used 400 instead of 60 000 on your data because there are no values even close to 60 000....
=SUMPRODUCT((B12:B63)*(E12:E63)*--(G12:G63="can")*--(H12:H63="filling")*((B12:B63)*(E12:E63)>400))
As a test, use a helper column (I used I) and copy this down...
=E12*B12
Then for the total (to test), use this...
=SUMIFS(I12:I63,H12:H63,"filling",G12:G63,"can",I12:I63,">400")
Thank you, I also tried the testing with I and the alternative formula you suggested was good. I learnt something new.
you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks