Dear,
I need to get the Sumproduct of two columns based on multiple conditions which relates to other columns one of which includes date also. I have attached the file for your reference. I need to the sumproduct of Columns "C & D", for those rows where :
Column A - Not Equal to "B"
Column B - Not Equal to "A"
Column D - Qty greater than Zero
Column E - Date Earlier than 01/01/2003
Column F - Not Equal to "D"
Column G - Not Equal to "C"
Column H - Blank Cells
For unit price:
=SUMPRODUCT(($A$2:$A$21<>"B")*(($B$2:$B$21<>"A")*($E$2:$E$21<"1/1/2003")*(($F$2:$F$21<>"D")*(($G$2:$G$21<>"C")*(($H$2:$H$21="")*(C2:C21))))))
For Qty:
=SUMPRODUCT(($A$2:$A$21<>"B")*(($B$2:$B$21<>"A")*($E$2:$E$21<"1/1/2003")*(($F$2:$F$21<>"D")*(($G$2:$G$21<>"C")*(($H$2:$H$21="")*(D2:D21>0))))))
If qty is has to be part for of the calculation then
=SUMPRODUCT(($A$2:$A$21<>"B")*(($B$2:$B$21<>"A")*($E$2:$E$21<"1/1/2003")*(($F$2:$F$21<>"D")*(($G$2:$G$21<>"C")*(($H$2:$H$21="")*(C2:C21))))))
Last edited by AlKey; 10-22-2013 at 09:29 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Or all in one formula (i.e. unit price * quantity where criteria are met)
=SUMPRODUCT((A2:A21<>"B")*(B2:B21<>"A")*(D2:D21>0)*(E2:E21<DATE(2003,1,1))*(F2:F21<>"D")*(G2:G21<>"C")*(H2:H21=""),C2:C21*D2:D21)
DELETED: Forum double posted my reply.
Last edited by BadlySpelledBuoy; 10-22-2013 at 09:01 AM.
Thanks a lot guys for the reply. But I was trying to do the same thing with the following formula, please tell me why it will not give me the same result:
=SUMPRODUCT(--(A2:A21<>"B"),--(B2:B21<>"A"),--(D2:D21>0),--(E2:E21<"01/01/2003"),--(F2:F21<>"D"),--(G2:G21<>"C"),--(H2:H21=""),(C2:C21),(D2:D21))
What result does that formula give you?
What result were you expecting?
I've just tried and it gives me the same result as my formula did, and from your original post it seems that's the result you require.
If i want to exclude "B" & "C" in Column "A",how should we amend the Formula ?
rGRDS.
hi suhabthan. for us to assist you better, i suggest you read our questions & answer them. this is what BadlySpelledBuoy asked in post #6
your formula seems to be correct too. but you can remove some unnecessary brackets & try not to use a date with slashes like that. if a computer's regional settings is in DMY, it reads 1/2/2003 as 1Feb2003. with MDY, it'll be 2Jan2003. so use BadlySpelledBuoy's DATE method or spell month in 3 letters:Originally Posted by BadlySpelledBuoy
Formula:
Please Login or Register to view this content.
and since you came up with that formula, you probably know how to insert 1 more criteria field?
Formula:Please Login or Register to view this content.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks