ExcelBanter - SUMIF division

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SUMIF division (https://www.excelbanter.com/excel-worksheet-functions/264211-sumif-division.html)

From Sheet 2, I'd like a formula to calculate the following using an array:

If Sheet1 Column A = Sheet2 Column A
AND
If Sheet1 Column K 0
THEN
Divide Sheet1 Column K by Sheet1 Column L

Can anyone help? Thanks.

Bob

=IF(A1=Sheet1!A1,IF(Sheet1!K10,Sheet1!K1/Sheet1!L1,0))

"bob" wrote:

From Sheet 2, I'd like a formula to calculate the following using an array:

If Sheet1 Column A = Sheet2 Column A
AND
If Sheet1 Column K 0
THEN
Divide Sheet1 Column K by Sheet1 Column L

Can anyone help? Thanks.

Bob

Assuming you are wanting a single-cell output, this array* formula will
work:

=SUM(IF((A2:A100=Sheet2!A2:A100)*(K2:K1000),K2:K1 00)/Sheet2!L2:L100)

Note that if any cell in L2:L100 is text or equal to zero, an error will
occur.
*Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter

--
Best Regards,

Luke M
"bob" wrote in message
...
From Sheet 2, I'd like a formula to calculate the following using an
array:

If Sheet1 Column A = Sheet2 Column A
AND
If Sheet1 Column K 0
THEN
Divide Sheet1 Column K by Sheet1 Column L

Can anyone help? Thanks.

Bob