sumif based on cell comparison in excel
 

Hi there,
I'm trying to sum up values in a table based on a subtraction calculation on
the row heading and column heading of a table.

For example:
If the data is something like
A B C D
1 1 2 3
2 1 9 99 999
3 2 8 88 888
4 3 7 77 777

I want to compare each dataitem in the first row, to each data item in the
first column such as: if (B1:D3 - A2:A4) <1 then sum cells B2:D4 (in the 3x3
data table with headings). In this example, the following values should be
summed (9;8;88;7;77;777)

Thanks for your help

sumif based on cell comparison in excel
 

"Bunty" skrev i en meddelelse
...
Hi there,
I'm trying to sum up values in a table based on a subtraction calculation
on
the row heading and column heading of a table.

For example:
If the data is something like
A B C D
1 1 2 3
2 1 9 99 999
3 2 8 88 888
4 3 7 77 777

I want to compare each dataitem in the first row, to each data item in the
first column such as: if (B1:D3 - A2:A4) <1 then sum cells B2:D4 (in the
3x3
data table with headings). In this example, the following values should
be
summed (9;8;88;7;77;777)

Thanks for your help



Hi Bunty

One way:

=SUMPRODUKT(((B1:D1-A2:A4)<1)*B2:D4)

--
Best regards
Leo Heuser

Followup to newsgroup only please.

sumif based on cell comparison in excel
 

"Leo Heuser" skrev i en meddelelse
...
"Bunty" skrev i en meddelelse
...
Hi there,
I'm trying to sum up values in a table based on a subtraction calculation
on
the row heading and column heading of a table.

For example:
If the data is something like
A B C D
1 1 2 3
2 1 9 99 999
3 2 8 88 888
4 3 7 77 777

I want to compare each dataitem in the first row, to each data item in
the
first column such as: if (B1:D3 - A2:A4) <1 then sum cells B2:D4 (in the
3x3
data table with headings). In this example, the following values should
be
summed (9;8;88;7;77;777)

Thanks for your help



Hi Bunty

One way:

=SUMPRODUKT(((B1:D1-A2:A4)<1)*B2:D4)

--
Best regards
Leo Heuser

Followup to newsgroup only please.



Sorry, that was the Danish version. Here's the English one:

=SUMPRODUCT(((B1:D1-A2:A4)<1)*B2:D4)

Not much difference, but enough to get a #NAME! error.

Leo Heuser