SUMPRODUCT IF value equals

[Frenzyy]

Hello, could you please kindly help me with the below, to make it so that it'd only consider values for the calculation if the corresponding value in column 'A' = "XX", and instead of specifying the range, sums up all the matched values in a given column?
Please also note that values start in row 4.

=SUMPRODUCT(XX4:XX150,$B$4:$B$150)/SUM(B4:B150)

I've tried using various IFs as well as SEARCH but couldn't get it to work, unfortuntely.

Many thanks!

 

[KRice]

I'm not sure that I follow your plans. This will compare values in column A with corresponding values in column XX, and if they are the same, then the corresponding value in column B will be added to the sum. Is this what you had in mind?

=SUMPRODUCT($B$4:$B$150,--(A4:A150=XX4:XX150))

 

[Frenzyy]

Hi KRice, thanks for the quick reply. Sorry for not explaining this well.

In column A, there could be several strings present, I'm only interested in one of them- "XX". Then, if a row has that "XX" under A, I want to sum values from two other columns, let's say B & C, and then divide the overall result by the values in C.

In essence, the total in cell D1 will contain the result of: IF A = XX Then SUMPRODUCT(B:B, C:C) divided by SUM(C:C)

Hope that helps but please let me know if you have questions.

Thanks!

 

[KRice]

Thanks for the clarification. I still have some questions, as there is a difference between "sum values from two other columns" and the formula you show (SUMPRODUCT(B:B, C:C)). The former would add corresponding values from columns B and C, while the latter will multiply them...and then the intermediate results from each of those row by row operations would be summed together. I'm guessing that you might want the latter, where column B value is multiplied by column C value if column A value = "XX". Then all of those intermediate products are summed and then divided by the total sum of column C.

=SUMPRODUCT(($B$4:$B$150)*($C$4:$C$150),--(A4:A150="XX"))/SUM($C$4:$C$150)

If want to add the values in columns B and C, rather than multiplying them, just change the "*" above to "+".

 

[Frenzyy]

This is great, thank you very much.

What would have to be done to get rid of having to specify the range? If you wouldn't mind, please.

What I mean is, at the moment we have the "4:150" declared but if the rows go beyond 150 then it has to be manually changed. I'm aware of the "A:A" but in this case it doesn't work, perhaps due to the fact that all the values being summed up, are derived from formulas and some are empty (or 'hidden' behind ""), and that's why #!VALUE or #N/A shows up as a result... Maybe something like an IF combined with ISNUMBER would work? Not sure if I'm going about this the right way though...

Thank you.

 

[KRice]

Try changing the * to a comma (,) as shown here. That still results in a multiplication, but it is multiplication within SUMPRODUCT, which treats blanks, like formula-generated blanks, as 0's, whereas the conventional * operator fails when encountering the same kind of blank. In the example, I have only 10-20 rows of data but relatively long ranges, so there should be no problems expanding the range well beyond your data table.

MrExcel20200419a.xlsx
	A	B	C
1	-	result -->	0.857143
2
3
4
5
6
7	12	1	4
8	red		5
9	XX	3	4
10	5	4
11	blue	5	6
12	XX	6	2
13	XX		11
14	XX	13
15	XX	2	3
16
17
Sheet10

Cell Formulas
Range	Formula
A1	A1	=IFERROR(IF(OR(B4="", C4=""), "-", "S"&RIGHT(YEAR(C4), 1)&TEXT(MONTH(C4), "00")&VLOOKUP(B4, ServiceTechs, 2, FALSE)&TEXT(SUMPRODUCT((INDIRECT("$B$4:$B$"&ROW(B4))=B4),(MONTH(INDIRECT("$C$4:$C$"&ROW(B4)))=MONTH(C4))),"00")), "-")
C1	C1	=SUMPRODUCT(($B$4:$B$150),($C$4:$C$150),--(A4:A150="XX"))/SUM($C$4:$C$150)
B13	B13	=IF(D13=4,TRUE,"")
C14	C14	=IF(D14=4,TRUE,"")

 

[Frenzyy]

Thank you KRice, works perfectly. I ended up changing the SUM at the end to SUMPRODUCT with the '--' exclusion too, due to the SUM taking into consideration all cells when dividing which was making the results a bit off but anyway, thanks again!

 

[KRice]

You're welcome...glad to help.