sumif from multiple columns

  • Thread starter Thread starter dcoxdcox
  • Start date Start date
D

dcoxdcox

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?
 
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 normallyuse
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?
Click to expand...

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