formula help

[kirkatron]

Hi guys I would say I'm pretty good at excel but this one is bugging me maybe I'm using the wrong formula?

=XLOOKUP("Royal Mail",'Cash Out'!A72:A138,'Cash Out'!C72:C138)

basically i need it to add up the output value (c72:c138)

as there are multiple of the same value in (a72:a138)

[#2 · Accepted solution]

@kirkatron Use FILTER in stead.

=SUM(FILTER('Cash Out'!C72:C138,'Cash Out'!A72:A138="Royal Mail"))

XLOOKUP only returns the first match it finds. Top-down or bottom-up but never more than one value, so you can't sum multiple occurrences.

[#3]

worked like a charm first time. i tried useing the sumifs too but didnt work. so many formulas and functions.

many thanks

[#4]

In reply to

=SUM(FILTER('Cash Out'!C72:C138,'Cash Out'!A72:A138="Royal Mail"))

if i were to want to search if (mail) was in a name in (A72:A138) how would i do that but including it into the formula above .

any help would be greatly appreciated

thanks #
kirk

[#5]

@kirkatron 

Do you mean that the name should contain mail ? If so:

=SUM(FILTER('Cash Out'!C72:C138,ISNUMBER(SEARCH("mail",'Cash Out'!A72:A138)))

 If you literally meant that it should contain (mail) :

=SUM(FILTER('Cash Out'!C72:C138,ISNUMBER(SEARCH("(mail)",'Cash Out'!A72:A138)))

[#6]

Perfect

=SUM(FILTER('Cash Out'!C72:C138,ISNUMBER(SEARCH("mail",'Cash Out'!A72:A138)))
is the one i wanted

i did IF(ISNUMBER(SEARCH

so glad i asked on this fourm i was pulling what hair i have left out.
thank you so much for your help.