Hi there,
I was wondering if it is possible to have a sumif with multiple conditions
example
col 1 col 2 col 3 col 4
123 A C 13
345 A D 12
567 A E 10
789 B F 23
Would it be possible to have it sumif col1 = 123 but then col 2 has to = A and col 3 can be in the following C,D,E, of F. Then sum col4 of those that meet that criteria?
I think you could write it using an and function. So then it would become something like:
=SUMIF(D1:D4,AND(A1:A4=123,B1:B4="a",C1:C4="c"))
I am not sure if this would work, but I think we are very close
Last edited by Kybynn; 04-20-2012 at 12:43 AM.
Will this helps you.
=SUMIFS(D1:D4,B1:B4,"A",C1:C4,"C")
hmm, will it allow me to put multiple variable for col3?
I'll have destination points in col3, so i wanted to be able to say calculate col4 if the destinations are tx, fl, ga, ca, and oh.
Given that your profile states 2003, and the limited data you have provided, you could try ...
If this isn't what you need, post a sample workbook showing typical data and your expected result.
=SUMPRODUCT(--(A2:A5=123),--(B2:B5=B2),D2:D5)
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
we just recently switched to 2010
i'll post a sample of the excel page for ya in a bit
dummy.xlsx
im looking at the excel you made and it's missing one thing.
Col 2 represents departure point and Col 3 represent destination point.
Col 1 would represent transportation number
So what i'm trying to do is have a formula that searches a specific transportation number ie 123
departing from point A and going to D,E,F, and C
so A->D
A ->E
A ->F
A ->C
and if it does going to either of those point then it would add the volume in Col 4.
Last edited by hawee; 04-21-2012 at 09:36 AM.
any luck guys?
Your sample is still somewhat vague.
Try these dynamic formulae. Your list can be any length.
If you need to work with 2003 then you will have to use SUMPRODUCT(), for 2007 and above only, use SUMIFS()
Again if this doesn't work for you, post a larger sample of your actual workbook.
Hey Marcol,
Thanks for the help.
It's really close to what I need it to do.
I've attached a file with a bigger sample.
The only thing wrong is it's calculating all destination points. I only want it to calculate destination point 1,2,3,4,5, and 6.
If you look at the file.
11203 should equal - 400, not 800
11205 should equal - 600, not 800
How to I specify only those points to be calculated.
Book1_hawee.xls
also its for Excel 2007
Use,Originally Posted by hawee
=SUM(SUMIFS(D:D,A:A,F3,B:B,G3,C:C,{1,2,3,4,5,6}))
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Okay, try this workbook
I don't have 2007 at the moment so that formula might need checking.
Almost there, lol
Sorry I guess I need to mention that I'll have to specify the points. It will be 1,2,3,4,5,6,10,12,13,15,20,24,42.
Is there a way to do that?
Thanks,
Harry
See the attached.
perfect!! ty very much
Neat solution Haseeb, couldn't give you rep for your idea, (seems I've been "bumping" your rep), so a "well done" will have to suffice.
Hey Guys,
I actually need to make a few tweaks and can't seem to figure it out, so i'm back to asking for some help.
Instead of have 1 departure, we have multiple departure
So in theory I would like it to let me know the volume of a contract base on specific departure and destination.
Is it possible to just add an index for the departure?
Thanks
any help please?
bump
See the attached.
help please
bump again
Hey Guys,
I actually need to make a few tweaks and can't seem to figure it out, so i'm back to asking for some help.
Instead of have 1 departure, we have multiple departure
So in theory I would like it to let me know the volume of a contract base on specific departure and destination.
Is it possible to just add an index for the departure?
Thanks
any help? please
hmm i see it working on the attacked excel sheet, but when i put it in my sheet it's giving me " #value! " as the response.
here's what i had to edit the code to line up with my sheet
=SUMPRODUCT((B$186:B$3000=J18)*(ISNUMBER(MATCH(D$186:D$3000,Seg!F$3:F$8,)))*(ISNUMBER(MATCH(K$186:K$3000,Seg!A$3:A$100,)))*I$186:I$3000)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks