| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM one column based on another column repeatedly
Consider the following:
Col A Col B Col C Col D ERDF 45,678 JKNM 23,234 JKNM 12,909 ABCD 54,567 ABCD 22,222 ERDF 11,111 I want to search every column for ERDF and sum the column to the right of it. Consider enough columns to reach column BZ. Thanks, casey |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM one column based on another column repeatedly
Try this:
=SUMIF(A1:C3,"ERDF",B1:D3) Notice how the ranges are staggered! -- Biff Microsoft Excel MVP "casey" wrote in message ... Consider the following: Col A Col B Col C Col D ERDF 45,678 JKNM 23,234 JKNM 12,909 ABCD 54,567 ABCD 22,222 ERDF 11,111 I want to search every column for ERDF and sum the column to the right of it. Consider enough columns to reach column BZ. Thanks, casey |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM one column based on another column repeatedly
Perfect! And so simple. I was trying to write a SUMIF using OFFSET.
Mucho Thanks!!! casey "T. Valko" wrote: Try this: =SUMIF(A1:C3,"ERDF",B1:D3) Notice how the ranges are staggered! -- Biff Microsoft Excel MVP "casey" wrote in message ... Consider the following: Col A Col B Col C Col D ERDF 45,678 JKNM 23,234 JKNM 12,909 ABCD 54,567 ABCD 22,222 ERDF 11,111 I want to search every column for ERDF and sum the column to the right of it. Consider enough columns to reach column BZ. Thanks, casey |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM one column based on another column repeatedly
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "casey" wrote in message ... Perfect! And so simple. I was trying to write a SUMIF using OFFSET. Mucho Thanks!!! casey "T. Valko" wrote: Try this: =SUMIF(A1:C3,"ERDF",B1:D3) Notice how the ranges are staggered! -- Biff Microsoft Excel MVP "casey" wrote in message ... Consider the following: Col A Col B Col C Col D ERDF 45,678 JKNM 23,234 JKNM 12,909 ABCD 54,567 ABCD 22,222 ERDF 11,111 I want to search every column for ERDF and sum the column to the right of it. Consider enough columns to reach column BZ. Thanks, casey |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM one column based on another column repeatedly
I just found an anomaly in this formula for my situation. I have numbers
also in the columns other than letters. And when they're all numbers, it adds up erroneously. So reconsider the following: Col A Col B Col C Col D 22 13 123 78 ERDF 12 HKHK 100 123 312 789 22 456 22 22 45 I need to add up the numbers in columns B & D based on the numbers (account numbers) in columns A and C. In your previous formula, if summing for account number "22", I would get an answer of 80 instead of 58 (It adds column C because it found a "22" in column B. I hope that's clear. Thanks, casey "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "casey" wrote in message ... Perfect! And so simple. I was trying to write a SUMIF using OFFSET. Mucho Thanks!!! casey "T. Valko" wrote: Try this: =SUMIF(A1:C3,"ERDF",B1:D3) Notice how the ranges are staggered! -- Biff Microsoft Excel MVP "casey" wrote in message ... Consider the following: Col A Col B Col C Col D ERDF 45,678 JKNM 23,234 JKNM 12,909 ABCD 54,567 ABCD 22,222 ERDF 11,111 I want to search every column for ERDF and sum the column to the right of it. Consider enough columns to reach column BZ. Thanks, casey |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM one column based on another column repeatedly
Ok, that makes it more complicated.
Try this: =SUMPRODUCT((MOD(COLUMN(A1:C4),2)=1)*(A1:C4=22),B1 :D4) Notice there is still a stagger in the ranges. -- Biff Microsoft Excel MVP "casey" wrote in message ... I just found an anomaly in this formula for my situation. I have numbers also in the columns other than letters. And when they're all numbers, it adds up erroneously. So reconsider the following: Col A Col B Col C Col D 22 13 123 78 ERDF 12 HKHK 100 123 312 789 22 456 22 22 45 I need to add up the numbers in columns B & D based on the numbers (account numbers) in columns A and C. In your previous formula, if summing for account number "22", I would get an answer of 80 instead of 58 (It adds column C because it found a "22" in column B. I hope that's clear. Thanks, casey "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "casey" wrote in message ... Perfect! And so simple. I was trying to write a SUMIF using OFFSET. Mucho Thanks!!! casey "T. Valko" wrote: Try this: =SUMIF(A1:C3,"ERDF",B1:D3) Notice how the ranges are staggered! -- Biff Microsoft Excel MVP "casey" wrote in message ... Consider the following: Col A Col B Col C Col D ERDF 45,678 JKNM 23,234 JKNM 12,909 ABCD 54,567 ABCD 22,222 ERDF 11,111 I want to search every column for ERDF and sum the column to the right of it. Consider enough columns to reach column BZ. Thanks, casey |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM one column based on another column repeatedly
Perfect! Next step for me is to break down what your formula and understand
why. Thanks a million!!! casey "casey" wrote: Consider the following: Col A Col B Col C Col D ERDF 45,678 JKNM 23,234 JKNM 12,909 ABCD 54,567 ABCD 22,222 ERDF 11,111 I want to search every column for ERDF and sum the column to the right of it. Consider enough columns to reach column BZ. Thanks, casey |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM one column based on another column repeatedly
You're welcome!
-- Biff Microsoft Excel MVP "casey" wrote in message ... Perfect! Next step for me is to break down what your formula and understand why. Thanks a million!!! casey "casey" wrote: Consider the following: Col A Col B Col C Col D ERDF 45,678 JKNM 23,234 JKNM 12,909 ABCD 54,567 ABCD 22,222 ERDF 11,111 I want to search every column for ERDF and sum the column to the right of it. Consider enough columns to reach column BZ. Thanks, casey |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting entries in column based on condition in another column | Excel Worksheet Functions | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| Text to column and selecting values based on a different column | Excel Worksheet Functions | |||
| move contents of column C based on criteria related to column A | Excel Discussion (Misc queries) | |||
| EXCEL Sum column based on time range in different column? | Excel Worksheet Functions | |||
Hybrid Mode
