Better Formula to Get Data Across Three Columns

[leaning]

Hello! This one has me stumped. I have a clunky formula that works, but I know there is something better:


Column A values: only "A", "B", "C", but no particular pattern down the column (ABBBAAACCACB, etc.)
Column B values: only "Add" "Increase" "Grow" "Subtract" "Remove" "Shrink"
Column C: Just numbers (all 10's for this example).

Task: For all the B's in Column A (for example), do what the B column says using the number in Column C.

I have this formula in E4 for the A result, but it's really wordy:

=SUMIFS(C2:C14, B2:B14, "Add", A2:A14, "A") +SUMIFS(C2:C14, B2:B14, "Increase", A2:A14, "A")+SUMIFS(C2:C14, B2:B14, "Grow", A2:A14, "A")-SUMIFS(C2:C14, B2:B14, "Subtract", A2:A14, "A")-SUMIFS(C2:C14, B2:B14, "Remove", A2:A14, "A")-SUMIFS(C2:C14, B2:B14, "Shrink", A2:A14, "A")

Sample attached.

I'd appreciate any help from the pros for shortening this up. Thanks!

Regards, Leaning

[Tony Valko]

Maybe this...

=SUM(SUMIFS(C2:C14,B2:B14,{"Add","Increase","Grow"},A2:A14,"A"))-SUM(SUMIFS(C2:C14,B2:B14,{"Subtract","Remove","Shrink"},A2:A14,"A"))

[leaning]

Tony,

That is a lot shorter and works. I didn't know about that curly-brackets thing. Thanks!

(BTW, what did we use before SUMIFS? (I have users who are still on Excel 97-03.))

Regards,

Leaning

[Tony Valko]

These examples will work in every version of Excel.

This array formula**:

=SUM(IF(B2:B14={"Add","Increase","Grow"},IF(A2:A14="A",C2:C14)))-SUM(IF(B2:B14={"Subtract","Remove","Shrink"},IF(A2:A14="A",C2:C14)))

Or, the same array formula** but using cells to hold the criteria...

E2:G2 = Add, Increase, Grow
E3:G3 = Subtract, Remove, Shrink

=SUM(IF(B2:B14=E2:G2,IF(A2:A14="A",C2:C14)))-SUM(IF(B2:B14=E3:G3,IF(A2:A14="A",C2:C14)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This normally entered version also using cells to hold the criteria...

=SUMPRODUCT(--ISNUMBER(MATCH(B2:B14,E2:G2,0)),--(A2:A14="A"),C2:C14)-SUMPRODUCT(--ISNUMBER(MATCH(B2:B14,E3:G3,0)),--(A2:A14="A"),C2:C14)

[FlameRetired]

For what it's worth here's another array formula that concatenates the "+"s and "-"s. It uses this lookup table in L1:M4.

Row\Col	L	M
1	Add	+
2	Increase	+
3	Remove	-
4	Subtract	-

Please Login or Register  to view this content.

Also committed CSE.