SumIf Array - Multiple search criteria, multiple sum columns

[CMA214]

Hey all, hoping someone can help me out.

I have a table, where in column A I have different departments (middle market, retail, etc.), each broken out by either "Operational xx" / "Stable xx", or "Non-operational xx" / "Non-Stable xx"

In columns E-P, I have a cash schedule broken out by months, ie. Month 1, Month 2, etc.

I have a formula that can Sum column E based upon whether column A says Operational or Stable. My problem is that I want to Sum columns E:P. Is this possible? I can't seem to figure it out. Here is the formula I am currently using:

{=SUM(SUMIF($A$4:$A$35,{"Operational*","Stable*"},$D$4:$D$35))}

I am only using the asterisks after the word, so it doesn't pick up the "Non" segments.

 

[Weazel]

maybe something to try...

=SUMPRODUCT((LEFT($A$2:$A$10,1)="o")*(E2:P10))

unfortunately the wildcards won't work with sumproduct and summing multiple ranges wont work with sumif

and maybe substitute the "o" with "s" for summing stable ?

 

[CMA214]

Not a bad idea... I suppose another way to go would be add a column to the left, and add 1s and 0s for a sumproduct to reference based on "stable/operational" or "non-stable".

Like you said, wildcards & multiple ranges simply may not work.

 

[pgc01]

Hi

There would also be other options, for ex. to compare the left part, a workaround for the XXX*:

=SUMPRODUCT(((LEFT($A$4:$A$35,LEN("Operational"))="Operational")+(LEFT($A$4:$A$35,LEN("Stable"))="Stable"))*$E$4:$P$35)

This could be made more compact if we had more options.

We could also use the SumIf() but since the ranges must have the same dimensions we'd have to repeat the first one as many times as the columns in the first one.