sumif from multiple columns
 

I have an list of items with multiple columns of values attached.

Col A = items number (all unique)
Col B = class number (the basis for summarizing)
Col C thru Z = daily values for each item
Row 1 = date

I want to be able to summarize by class number (which I would normally use
SUMIF) and for selected colums (i.e. one or more days)

In other words - what is the sum of class X on day Y

what formula or formulas would I use?

sumif from multiple columns
 

On Feb 1, 11:06*pm, dcoxdcox
wrote:
I have an list of items with multiple columns of values attached.

Col A = items number (all unique)
Col B = class number (the basis for summarizing)
Col C thru Z = daily values for each item
Row 1 = date

I want *to be able to summarize by class number (which I would normally use
SUMIF) and for selected colums (i.e. one or more days)

In other words - what is the sum of class X on day Y

what formula or formulas would I use?

It depends what version of Excel you are using, in 2007 you just use
the function sumif, but since you haven't figured that out I'm
guessing you aren't.
Try copying this one below the date column you want to sum:
=SUMPRODUCT(($B$2:$B21="X")*(C$2:C21))

Per Erik

sumif from multiple columns
 

sumproduct is more complete than sumif and you can perform conditional sum
with more than one condition easily.
In this case use can use:
sumproduct(--(C2:C10="daily value")),--(B2:B10="class number"))
This will give sum of class numbers for specified daily value. You can add
as much much as daily value you want.

"dcoxdcox" wrote:

I have an list of items with multiple columns of values attached.

Col A = items number (all unique)
Col B = class number (the basis for summarizing)
Col C thru Z = daily values for each item
Row 1 = date

I want to be able to summarize by class number (which I would normally use
SUMIF) and for selected colums (i.e. one or more days)

In other words - what is the sum of class X on day Y

what formula or formulas would I use?

sumif from multiple columns
 

I guess I'm a little out of date- using Excel 2003

"Per Erik Midtrød" wrote:

On Feb 1, 11:06 pm, dcoxdcox
wrote:
I have an list of items with multiple columns of values attached.

Col A = items number (all unique)
Col B = class number (the basis for summarizing)
Col C thru Z = daily values for each item
Row 1 = date

I want to be able to summarize by class number (which I would normally use
SUMIF) and for selected colums (i.e. one or more days)

In other words - what is the sum of class X on day Y

what formula or formulas would I use?

It depends what version of Excel you are using, in 2007 you just use
the function sumif, but since you haven't figured that out I'm
guessing you aren't.
Try copying this one below the date column you want to sum:
=SUMPRODUCT(($B$2:$B21="X")*(C$2:C21))

Per Erik

sumif from multiple columns
 

I've seen other threads that pose this solution, but if I understand
correctly, this works if the data is organized in 4 columns A=item number,
B=class number, C=date, D=value.

What I have is many columns with each column being the values for 1 day. I
need a formula that I can specify a class and a date or range of dates (i.e.
columns)

As noted in another reply, I'm using Excel 2003


"Khoshravan" wrote:

sumproduct is more complete than sumif and you can perform conditional sum
with more than one condition easily.
In this case use can use:
sumproduct(--(C2:C10="daily value")),--(B2:B10="class number"))
This will give sum of class numbers for specified daily value. You can add
as much much as daily value you want.

"dcoxdcox" wrote:

I have an list of items with multiple columns of values attached.

Col A = items number (all unique)
Col B = class number (the basis for summarizing)
Col C thru Z = daily values for each item
Row 1 = date

I want to be able to summarize by class number (which I would normally use
SUMIF) and for selected colums (i.e. one or more days)

In other words - what is the sum of class X on day Y

what formula or formulas would I use?

sumif from multiple columns
 

How are you selecting which days to sum? Is the date you want to select in
row 1 of the particular column? And how do you specify which date(s) to
select?

Regards,
Fred.

"dcoxdcox" wrote in message
...
I've seen other threads that pose this solution, but if I understand
correctly, this works if the data is organized in 4 columns A=item number,
B=class number, C=date, D=value.

What I have is many columns with each column being the values for 1 day.
I
need a formula that I can specify a class and a date or range of dates
(i.e.
columns)

As noted in another reply, I'm using Excel 2003


"Khoshravan" wrote:

sumproduct is more complete than sumif and you can perform conditional
sum
with more than one condition easily.
In this case use can use:
sumproduct(--(C2:C10="daily value")),--(B2:B10="class number"))
This will give sum of class numbers for specified daily value. You can
add
as much much as daily value you want.

"dcoxdcox" wrote:

I have an list of items with multiple columns of values attached.

Col A = items number (all unique)
Col B = class number (the basis for summarizing)
Col C thru Z = daily values for each item
Row 1 = date

I want to be able to summarize by class number (which I would normally
use
SUMIF) and for selected colums (i.e. one or more days)

In other words - what is the sum of class X on day Y

what formula or formulas would I use?

sumif from multiple columns
 

Hi,

To pick the dates set up a range with all the dates you have from C1:Z1. I
set them up vertically in A19:A42. When you want to include the date enter
True in column B next to the dates. In A18 enter the Class Number you want
summed.

Then use the following array formula where B2:B16 are the Class Numbers,
C2:Z16 is all the data under the dates:

=SUM(($B$2:$B$16=A18)*($C$2:$Z$16)*(TRANSPOSE(($B$ 19:$B$42=TRUE)*($A$19:$A$42))=$C$1:$Z$1))

To make it an array you must press Shift+Ctrl+Enter not Enter. Also note
that you can use SUMPRODUCT but you will still need to enter it as an array.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"dcoxdcox" wrote:

I've seen other threads that pose this solution, but if I understand
correctly, this works if the data is organized in 4 columns A=item number,
B=class number, C=date, D=value.

What I have is many columns with each column being the values for 1 day. I
need a formula that I can specify a class and a date or range of dates (i.e.
columns)

As noted in another reply, I'm using Excel 2003


"Khoshravan" wrote:

sumproduct is more complete than sumif and you can perform conditional sum
with more than one condition easily.
In this case use can use:
sumproduct(--(C2:C10="daily value")),--(B2:B10="class number"))
This will give sum of class numbers for specified daily value. You can add
as much much as daily value you want.

"dcoxdcox" wrote:

I have an list of items with multiple columns of values attached.

Col A = items number (all unique)
Col B = class number (the basis for summarizing)
Col C thru Z = daily values for each item
Row 1 = date

I want to be able to summarize by class number (which I would normally use
SUMIF) and for selected colums (i.e. one or more days)

In other words - what is the sum of class X on day Y

what formula or formulas would I use?