How to use sumif on two columns (not sumifs)

[eeps24]

I have two columns I want to search for my criteria. For example...

L5 will contain the total for Joe. You can see Joe can be found under the "admit" column OR (not AND) the "discharge" column. He will never be in both at the same time in a particualar row. Any where Joe is, I want to copy the sum all the values from the "split" column.

So L5 should contain 3.72

I thought of sumifs but sumifs uses the AND. I want to look for Joe in either column.... Admit column OR Discharge column.

Any thoughts? I tried sumif and OR but couldn't figure it out.

Thank you



Capture.PNG

[Mvaldesi]

EDIT: Wrong Thread, sorry!

[Jonmo1]

It seems to me with this type of data, you would want to have separate summations for Admit and Discharge.
So you have one cell that does the sumif on column A, and another does the sumif on column B, then add the 2 together..

So in say L5 put
=SUMIF(A:A,K5,H:H)
and in M5 put
=SUMIF(B:B,K5,H:H)
then in N5 put
=L5+M5

[63falcondude]

In L5, try

=SUMPRODUCT((C$6:D$7=K5)*(H$6:H$7))

[gmr4evr1]

Something like..

Please Login or Register  to view this content.

[eeps24]

sorry guys i am having bad luck... i will upload my excel sheet, can someone please attempt their solution in my sheet, that way I dont mess anything up.

Thank you

[Jonmo1]

Try Like this

[gmr4evr1]

Per you sample, try this in L5 as 63falcondude suggested. I changed the columns in the 1st part to reflect your sample.

Please Login or Register  to view this content.

[63falcondude]

Whoops, I didn't notice those hidden columns.

Adjusting the formula as gmr4evr1 showed in post #8 should work for you.

[avk]

Try with

Please Login or Register  to view this content.

[eeps24]

wow, you are all right lol. I tried all your suggestions and they worked. Like they say, more than 1 way to skin a cat. Thank you all. I just gotta figure out which way I want to go.

[Jonmo1]

You're welcome

[gmr4evr1]

You're welcome.

[63falcondude]

Glad we could help. Thanks for the rep!