SUMIFS ( one column which contains multiple *text* & * text*)

Hi Guys,

I cant seem to figure this one out. Looked at several solutions also on this forum but none work for me. Let me try to explain. It sounds so easy though but i cant seem to make it work

A1 = *directory* (text) (constants which i can change)
A2 = *10* (text)
A3 = Rotterdam

What i want is that it sums up the values of B column where C contains "directory" AND "10" AND D column contains "Rotterdam"

outcome = B4 + B8 = 5 + 3 = 8

I tried this already (i saw online they said use arrays but this gives me an error)

excel2.jpg

Could someone please help me out here? I want to make use of the changable constants (A1 and A2) because i have to do this scenario over with just a small change in values.
Any help would be appreciated!

Look at the excel below

Find Excel here

example2.xlsx

Can you post an Excel sheet? It's impossible to tell what values are in which cell!!

Not really. It looks like there is no text in column B, just numbers.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Ive adjusted the picture and the text below. I hope this clarifies a bit for you sorry for the trouble

This version should give you 8 on the attached workbook

=SUMIFS(B4:B9,C4:C9,"*"&A2&"*",C4:C9,"*"&A3&"*")

That formula looks OK - I don't get an error - you shouldn't get errors with that unless you have some error values in the referenced ranges

Edit: in your revised sheet I used this version to get the result 8

=SUMIFS(B5:B10,C5:C10,"*"&A2&"*",C5:C10,"*"&A3&"*",D5:D10,"*"&A4&"*")

You can use whole columns in SUMIFS, if you wish, but all ranges need to be the same size and shape, so if you use the whole column for one range you need to do the same with all of them.