Excel Formula - SUMIFS with multiple criteria in one column of an excel table

[RCBricker]

Good morning.

I am having trouble finding a solution. I need to sum the sumifs of some data that is in a table. I don't want to convert it to a range. there is only one criteria per column except the last column. There I have multiple criteria to sum together. I usually would use something like:

=sum(sumifs(sum_range,Criteria1_range,criteria1,criteria2_range,{criteria2_A, criteria2_B}))
[\code]

this results in a warning that excel has found a problem with my formula.  

For now I am using the work around of:

[code]
=sum(sumifs(sum_range,Criteria1_range,criteria1,criteria2_range,criteria2_A)+sumifs(sum_range,Criteria1_range,criteria1,criteria2_range,criteria2_B)))
[\code]

this works fine, however, I will be needing a sumifs with about 10 criteria in one column.  So the work around gets a bit inefficient.

any help would be greatly appreciated.

thanks,

 

[pgc01]

Hi

What are criteria2_A and criteria2_B?

Remark:in the second case (the workaround) do you need the sum()?

 

[RCBricker]

Hi

What are criteria2_A and criteria2_B?

Remark:in the second case (the workaround) do you need the sum()?

don't know if you need the sum. I haven't tried to just add them without it.

As for what are the two criteria, they are the criteria that I need to add together to complete the sumifs I am trying to do.

if I wanted to sum all the sales for automobiles at my dealership, then I could just sum the amount column. Or I could sum certain types of cars like Honda. Or I could sum just Honda trucks. All of these work fine with a table. However, if I wanted to just sum white Honda trucks and black Honda trucks. That creates the result I am getting. Normally I would write it

=sum(sumifs("sales",model,honda,color,{"black", "White"}))

This would work on a range of data where I want to sum all the sales for all black and white Hondas. It doesn't work on a table of data. I am sure there is a way to write this for a table and as such I am seeking advice or guidance.

Thanks,

 

[XOR LX]

=sum(sumifs(sum_range,Criteria1_range,criteria1,criteria2_range,{criteria2_A[COLOR=#ff0000][B],[/B][/COLOR] criteria2_B}))
[\code]

this results in a warning that excel has found a problem with my formula. [/QUOTE]

Perhaps you are not using a version of Excel (e.g. English language) for which the comma is the separator for horizontal arrays?

If so, either use the appropriate syntax for your version, or else use a semicolon, which, as far as I know, is universally used as the separator for vertical arrays.

Regards

 

[pgc01]

Hi

As for what are the two criteria, they are the criteria that I need to add together to complete the sumifs I am trying to do.

That is what criteria are for.

What I meant is what are criteria2_A and criteria2_B?
are they constants like "black", "white"?
are they references to cells like A3,D4?
are they named ranges that reference cells?
are they named constants?

=sum(sumifs("sales",model,honda,color,{"black", "White"}))

The formula you posted with "black" and "white" works also with values in tables unless you have some syntax error.
Examples of errors:
- in your regional settings the comma is not an array separator
- honda is a horizontal array with more than 2 values
- the criteria values are not constants

Please clarify with a small formula with only constants and references to ranges that does not work.

 

[XOR LX]

Examples of errors:
- honda is a horizontal array with more than 2 values

Hi PGC,

Actually that syntax would be valid, in the sense that it would not error. In fact, the smaller of the two horizontal arrays would be extended such that its dimensions matched those of the larger, the additional entries in which all being assigned the error value #N/A.

For example, if honda was defined as J1:M1, i.e. a 4-cell horizontal range, then Excel would automatically redimension the color array

{"black","White"}

to

{"black","White","#N/A","#N/A")

i.e. also a 4-cell horizontal range.

In fact, if the color range included #N/A errors, these would be validly considered for summation as part of such a construction, i.e. for rows where the model is equal to either of the values in L1 and M1 and the color is #N/A.

Unthinkable in practice, of course, though interesting from a theoretical point of view!

Cheers

 

[RCBricker]

Hi PGC,

Actually that syntax would be valid, in the sense that it would not error. In fact, the smaller of the two horizontal arrays would be extended such that its dimensions matched those of the larger, the additional entries in which all being assigned the error value #N/A.

For example, if honda was defined as J1:M1, i.e. a 4-cell horizontal range, then Excel would automatically redimension the color array

{"black","White"}

to

{"black","White","#N/A","#N/A")

i.e. also a 4-cell horizontal range.

In fact, if the color range included #N/A errors, these would be validly considered for summation as part of such a construction, i.e. for rows where the model is equal to either of the values in L1 and M1 and the color is #N/A.

Unthinkable in practice, of course, though interesting from a theoretical point of view!

Cheers

hmmm looks like this might turn into an interesting conversation.

As for the formula. Apparently, the error was mine. I also must have continued to write the same error yesterday afternoon. The formulas work fine now.

 

[pgc01]

Actually that syntax would be valid, in the sense that it would not error.

You are right, this would not be a syntax error, it would probably be a misunderstanding of how the function works.
We see it a lot with 2 OR criteria, in which the arrays must be orthogonal.

 

[pgc01]

The formulas work fine now.

I'm glad you figured it out.