Combine formula into one formula

[howard]

I have the branch names in Col A and the criteria in U6

I want to sum the values in Col Q & R, based on the branch names in Col A pertaining to the branch name in U6

=SUMIFS(Q:Q,A:A,U6)+SUMIFS(R:R,A:A,U6)

the above formula gives me the correct result, but I would like to make combine the two formula's into one formula



Your assistance is most appreciated

 

[jproffer]

Maybe this:

=SUMIFS(Q:R,A:A,U6)

Nevermind that doesn't work. Sorry . Still tinkering...stand by

 

[howard]

thanks for the reply. Your formula results in #value !

 

[jproffer]

Ok, this works and I guess it's technically one formula....but really so was your original

=SUM(SUMIFS(Q:Q,A:A,U6),SUMIFS(R:R,A:A,U6))

 

[mikerickson]

How about =SUMPRODUCT(--(A:A=U6)*(Q:Q+R:R))

Although the OP is superior. It clearly shows what it is doing and is easy to edit in the future.

 

[howard]

Thanks for the help, much appreciated

 

[howard]

Thanks mikerickson for your formula. It unfortunately returns #Value !

the =sum(Sumifs.... formula provided by jproffer, works perfectly. Not sure why the sumproduct formula does not work