Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 266
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 266
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Significant number rounding based on key cell Slashman Excel Worksheet Functions 2 August 27th 06 11:04 PM
can excel insert rown based on variable data in a cell? SSBG Excel Worksheet Functions 1 August 6th 06 01:30 PM
Urgent date/scheduling calc needed jct Excel Worksheet Functions 3 February 24th 06 02:36 AM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 06:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"