SUMIF Formula Question

[amxtomzo]

I would like to shorten this formula and I keep doing somthing dumb. If someone has a moment could you please show me the light. I'll just bet I can use something different also.

Thank you very much for your help I do appreciate it

Thomas

=SUMIF(B2:B18, "SC",A2:A18)+SUMIF(B2:B18, "UNIVSTY",A2:A18)+SUMIF(B2:B18,"NIE",A2:A18)+SUMIF(B2:B18,"DEALER",A2:A18)

 

[Richard Schollar]

Hi

Perhaps:

=SUM(SUMIF(B2:B18,{"SC","UNIVSTY","NIE","DEALER"},A2:A18))

or

=SUMPRODUCT(ISNUMBER(MATCH(B2:B18,{"SC","UNIVSTY","NIE","DEALER"},0))+0,A2:A18)

 

[dblinn]

Hi there,

You could list your criteria to sum in a column and do a sumif formula againt them. If the criteria is sitting in Column D then formula looks lik this:

SC =sumif(B2:B18,D2,A2:A18)
UNIVSTY =sumif(B2:B18,D3,A2:A18)
NIE =sumif(B2:B18,D4,A2:A18)
DEALER =sumif(B2:B18,D5,A2:A18)

 

[T. Valko]

I would like to shorten this formula and I keep doing somthing dumb. If someone has a moment could you please show me the light. I'll just bet I can use something different also.

Thank you very much for your help I do appreciate it

Thomas

=SUMIF(B2:B18, "SC",A2:A18)+SUMIF(B2:B18, "UNIVSTY",A2:A18)+SUMIF(B2:B18,"NIE",A2:A18)+SUMIF(B2:B18,"DEALER",A2:A18)

You're better off if you use cells to hold the criteria:

• D2 = SC
• D3 = UNIVSTY
• D4 = NIE
• D5 = DEALER

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B18,D2:D5,0))),A2:A18)

 

[amxtomzo]

Richard, dblinn, T.Valko

Thanks so much all are great and I will figure which one works best with our code and forms.

Thanks again, this is a big help to me.

Thomas