Sumproduct - multiple conditions

[petrcz]

Hi guys,

pleae, can you give me a hint, how to proceed sumproduct with multiple conditions?

I have this table

Month	Average position	Impressions
2015|11	0	1000
2015|12	1	2000
2016|01	2	1500

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I need this: if value in column Month equals to "2015|11" and a value in the column "average position" is greater than 0, then use that data

Thank you in advance for any help...

RP

 

[sunny102]

Use the following formula

=LOOKUP(2,1/(A2:A100="2015|11")/(B2:B100=0),(C2:C100))

This will return the matching value from column C

 

[petrcz]

Thanks, but I need to calculate weightened average.
What is the average position (weightened average) regard to the number of impressions in a specific month?
PR
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">weighted average</body>

 

[godsaaint]

I don't completely understand what you mean, can you give an example with the desired output?

Anyhow, I guess you need something similar to this?: =SUMPRODUCT(--(A2:A4="2015|11")*--(B2:B4>0)*C2:C4)

 

[Eric W]

Perhaps:

=SUMPRODUCT(B2:B10,C2:C10,--(A2:A10="2015|11"))/SUMIF(A2:A10,"2015|11",B2:B10)

 

[petrcz]

OK, what is the weightened average of average position?
I need to calculate weightened average for month 2015|11 and for non "null" position. So, in this case, bold values should be ignored, because they are either "null" or are from different month..
The result of sample below is 1,66

Month	Average position	Impressions
2015|11	0	1000
2015|11	1	2000
2015|11	2	4000
2015|12	1	2000
2016|01	2	1500

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thank you
PR