Excel Question

[gbpfan1231]

I am trying to create a formula in Excel that will sum up multiple columns of data based on multiple criteria.

 

Example: I want to sum all columns of data Columns B,C and D if column A = "A" AND Row 1 = "JAN" With the below data the answer would be 27 and if you changed FEB to JAN it would automatically change the answer to 45

 

 

 

Code JAN JAN FEB

A 10 12 14

A 2 3 4

C 5 6 7

D 1 2 3

 

I have tried SUMPRODUCT and even a Sum(IF.....) with Ctrl Shift Enter but having trouble with the multiple criteria of Code = "A" AND Row 1 = "JAN"

 

TIA

[The Irish Doggy]

I thought consolidate would work, but its doubling the answer for JAN. Hopefully someone else has an idea...

[Gonkis]

I had to use 2 sumif statements, basically summing the Codes and then the months.

 

What about sumifs which allows for multiple criteria. I'll try that.

[Big Country]

Is there a need to have multiple columns with the same label (ie, the multiple columns with JAN as the header)? If you could consolidate the data such that all entries under JAN would fall under JAN, or even better yet have a separate column for month name, then you could use a pivot table to do this for you.

 

Assuming there is a reason you can't do this to make it really easy, then I'll keep looking at ways utilizing sum and if or sumproduct, as sumif is limited to one criteria.

Edited March 12, 2010 by Big Country

[Gonkis]

Is there a need to have multiple columns with the same label (ie, the multiple columns with JAN as the header)? If you could consolidate the data such that all entries under JAN would fall under JAN, or even better yet have a separate column for month name, then you could use a pivot table to do this for you.

 

Assuming there is a reason you can't do this to make it really easy, then I'll keep looking at ways utilizing sum and if or sumproduct, as sumif is limited to one criteria.

 

 

I was thinking the same thing and the reason my first sumif simply consolidated the A rows, second added the month columns.

[gbpfan1231]

Is there a need to have multiple columns with the same label (ie, the multiple columns with JAN as the header)? If you could consolidate the data such that all entries under JAN would fall under JAN, or even better yet have a separate column for month name, then you could use a pivot table to do this for you.

 

Assuming there is a reason you can't do this to make it really easy, then I'll keep looking at ways utilizing sum and if or sumproduct, as sumif is limited to one criteria.

I am stuck with the data I have in the way that it is. The problem is that someone created this huge file with data not thinking of the best way to lay it out so now I am stuck trying to figure out a way to do this.

[Big Country]

I trust you will want to be able to do this for multiple combinations, ie Code C and Feb etc., not just A and JAN?

 

If so, I think I have it, give me a few minutes.

 

If you want, PM me your e-mail addy and I can send a sample workbook, it's essentially utilizing the SUMPRODUCT function and applying the multiple criteria. Fine tuning now to make sure it is working as designed

[westvirginia]

I would probably use an if/then statement. You'll have to embed one within another but I do that all the time.

[CaP'N GRuNGe]

I would probably use an if/then statement. You'll have to embed one within another but I do that all the time.

 

That's what I was thinking.

[Big Country]

Here is a solution.

 

Using the sample data you posted above, I pasted it into cells C1:F5

 

Then, in cell A8 I entered Month:, in cell B8, JAN and in C8 the formula =SUMPRODUCT((D1:F1=B8)*D2:F5) Result is 41

 

Then, in cell A9 I entered CODE:, in cell B9, A and in C9 the formula =SUMPRODUCT((C2:C5=B9)*D2:F5) Result is 45

 

Then in cell A10 I entered Month and Code and in C10 the formula =SUMPRODUCT((D1:F1=B8)*(C2:C5=B9)*D2:F5) Result is 27

 

You merely change the entry in cell B8 or B9 to change your criteria

 

I am pretty sure you could set the formulas up on a separate sheet and reference your data set.

[gbpfan1231]

I trust you will want to be able to do this for multiple combinations, ie Code C and Feb etc., not just A and JAN?

 

If so, I think I have it, give me a few minutes.

 

If you want, PM me your e-mail addy and I can send a sample workbook, it's essentially utilizing the SUMPRODUCT function and applying the multiple criteria. Fine tuning now to make sure it is working as designed

PM sent - a sample would be awesome. Thanks

[Big Country]

Sample sent.

[Brentastic]

I was also thinking SUMPRODUCT or a CSE formula.