sumif based on cell comparison in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Bunty said:
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
Click to expand...


Hi Bunty

One way:

=SUMPRODUKT(((B1:D1-A2:A4)<1)*B2:D4)
 
Leo Heuser said:
Hi Bunty

One way:

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

--
Best regards
Leo Heuser

Followup to newsgroup only please.
Click to expand...


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
 
Back
Top