Forum Discussion

Copper Contributor

Oct 21, 2022

Solved

I am trying to search and sum values

Hi, I am tying to make a search and sum some datebase. for example: A B C D E fekaufland 5 Namirnice wolt 6 kaufland kaufland 4 lidl konzum 8 k...

Formulas and Functions

Riny_van_Eekelen
Oct 21, 2022
Carpusina The SEARCH part in your second formula returns an array of numbers or #VALUE errors. The IF function gets rid of the errors leaving a number when the words are matched in the same row. In your example only row5 has matching words "konzum" in both column A and D. The array that will be returned has three blanks and the number 8. SUM then returns 8.

You probably want to make use of a dynamic array function that will spill the result all at once.

Try this one in E2 and see if that does what you want:

=SUMIF(A2:A5;"*"&D2:D5&"*";B2:B5)

Riny_van_Eekelen

Platinum Contributor

Oct 21, 2022

Carpusina Then that could be:

=SUM(SUMIF(A2:A5;"*"&D2:D5&"*";B2:B5))

Copper Contributor

Oct 21, 2022

I have one more question if you maybe know. Can I somehow add condition that wont look at blank cells in column D?

Riny_van_Eekelen
Platinum Contributor
Oct 21, 2022
Carpusina The easiest would be to make sure you don't have blanks in column D. Why would you include blanks in a list to keywords to search for?

While writing this, I notice that HansVogelaar came up with a solution that excludes blanks.
HansVogelaar
MVP
Oct 21, 2022
Carpusina

=SUM(ISNUMBER(SEARCH(TRANSPOSE(D2:D5),A2:A5))*(TRANSPOSE(D2:D5)<>"")*B2:B5)