Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Significant number rounding based on key cell | Excel Worksheet Functions | |||
can excel insert rown based on variable data in a cell? | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions |