Spencer, A very helpful formula trick. Syntax you used: Sumproduct(--(ConditionRange=ConditionValue), Array1, Array2)/Sumif(Array2,Criteria, SumRange). I was wondering if this two dashes - - (ConditionRange=ConditionValue) works on any formula that you know off?
Hi Spencer, this video was incredible! I wanted to ask as well… if you wanted to do this in between dates, how would you incorporate dates into this formula please?
hi , what if I have multiple criterias apart from tenant type , say for example , the floor which the tenant is one? Can I use the same formula? I figure I can use sumifs for the divisor, but can will the numerator change accordingly?
Sure. We added this in the blog post (www.adventuresincre.com/conditional-weighted-average-sumproduct-sumif/) where we share the file. But the formula for two conditions is: = SUMPRODUCT(((ConditionArray1 = “Condition1”) * (ConditionArray2 = “Condition2”)), Array1, Array2) / SUMIFS(Array1, ConditionArray1, “Condition1”, ConditionArray2, “Condition2”)
Hi Spencer, thank you so much for this. I have a quick question. can i possibly use two conditions in one formula assuming some rents are $0 and i would like to calculate the weighted average rent by tenant type. Thanks this is very helpful
this video save my day
Thank you sir! I have used this video several times since it's original posting.
I finished my project thanks to you, very clear thanks!
Dude this video helped me a lot. Many thanks.
I was stuck trying to calculate a weighted average on two data arrays for dates in the last 30 days.
Your video helped a ton! Mahalo!
Fantastic formula. Exactly what I was looking for. Thank you!!
FANTASTIC! Thank you so much for posting this. Subscribed. Thanks!
Exactly what I am looking for. Thank you so much for this fantastic video.
Thank you so much, very helpful.
I was dividing by SUM. instead of SUMIF.
Thankyou!!!
You are the goat. Thanks dude saved me a lot of erroneous calculations in my model!
Muito obrigado! procurei em muitos lugares e em nenhum consegui resolver, mas o seu video me ajudou! sou do Brasil, MUITO OBRIGADO!!!
YOU ARE A LIFESAVER Thank you Sir Stay Safe
This was great!! Exactly what I was looking for
this is useful SO much! Thanks for this tutorial.Need to share it for sure
Excelent video! saved me a headache :)
the best, thank you, gracias mi hermano me salvaste
A la orden
Spencer,
A very helpful formula trick.
Syntax you used: Sumproduct(--(ConditionRange=ConditionValue), Array1, Array2)/Sumif(Array2,Criteria, SumRange).
I was wondering if this two dashes - - (ConditionRange=ConditionValue) works on any formula that you know off?
very useful thank you so much
Thank you, very useful.
Hi! Amazing video! You helped me a lot! Do yo know if it’s possible to put more than 1 conditional? How can I do it?
Thank you!
Hi Spencer, this video was incredible! I wanted to ask as well… if you wanted to do this in between dates, how would you incorporate dates into this formula please?
hi , what if I have multiple criterias apart from tenant type , say for example , the floor which the tenant is one? Can I use the same formula? I figure I can use sumifs for the divisor, but can will the numerator change accordingly?
how do you implement 2 conditions? I want a weighted average based on year and month from my data sheet?
Sure. We added this in the blog post (www.adventuresincre.com/conditional-weighted-average-sumproduct-sumif/) where we share the file. But the formula for two conditions is: = SUMPRODUCT(((ConditionArray1 = “Condition1”) * (ConditionArray2 = “Condition2”)), Array1, Array2) / SUMIFS(Array1, ConditionArray1, “Condition1”, ConditionArray2, “Condition2”)
Hi Spencer, thank you so much for this. I have a quick question. can i possibly use two conditions in one formula assuming some rents are $0 and i would like to calculate the weighted average rent by tenant type. Thanks this is very helpful
Yeah you can. Follow this:
Sumproduct ( - - (conditionrange = condition value), - - (condition range > 0), array1, array2,...)
its not working
@@asutoshpatel4436
@@dineshhotchandani2033 Hmm that's odd. Can you screenshot what you're inputting in the field? And what your goal is?
@@asutoshpatel4436 now it's working... I missed the coma(,) between two conditions... thanks a lot...it really helped me...
@@dineshhotchandani2033 ofc anytime! Knowledge should be free to all those who wanna learn :)