sumif on non contiguous cells

[LFKim2018]

I have named cells A1,B2,C3,D4 as "lfk"
these cells have some values
when I used =SumIf(A1:D4,lfk)
- I didn't get anything, what is wrong with the formula?
many many thanks

 

[Tom Urtis]

array (Ctrl+Shift+Enter)
=SUM(A1:D4*(ROW(A1:D4)=COLUMN(A1:D4)))

or non array (just Enter)
=SUMPRODUCT(N(OFFSET(A1,ROW(INDIRECT("1:4"))-1,ROW(INDIRECT("1:4"))-1)))

 

[LFKim2018]

Mr. Urtis
thank you for your prompt reply.
your formula worked.
my question is - how can I use the cell name into the formula?
is it possible?
many thanks

 

[Tom Urtis]

I must not be understanding what the problem is.
Just now I named a range lfk which I defined in the Name Manager as =Sheet1!$A$1,Sheet1!$B$2,Sheet1!$C$3,Sheet1!$D$4
Then I entered the formula
=SUM(lfk)
which returned the same result as both the formulas I posted, which was what I expected.
What am I missing about what you are expecting?

 

[LFKim2018]

Mr. Urtis

I just received your message:

Just now I named a range lfk which I defined in the Name Manager as =Sheet1!$A$1,Sheet1!$B$2,Sheet1!$C$3,Sheet1!$D$4
Then I entered the formula
=SUM(lfk)

This is the formula I am looking for.
many many thanks

 

[Tom Urtis]

Thanks.
I neglected to mention but you probably know, if the sheet of interest is not named Sheet1 then modify the sheet name accordingly in the Name Manager for the defined name.

 

[LFKim2018]

thank you..