Forum Discussion
I am trying to search and sum values
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)
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)
Hi, thank you for response. Your formula search and sum values but put answer in each row of searched word. I would like to put sum of those words that are found in one cell.
For example that I gave, I would like to put it in E1 and that results would need to be 17.
I hope I explained good.I tried to add sum before and worked great.
Thank you very much.
Or, using your original formula:
=SUM(ISNUMBER(SEARCH(TRANSPOSE(D2:D5),A2:A5))*B2:B5)
or
=SUM(IF(ISNUMBER(SEARCH(TRANSPOSE(D2:D5),A2:A5)),B2:B5))
If you don't have Office 2021 or Microsoft 365, confirm the formula by pressing Ctrl+Shift+Enter
- I have one more question if you maybe know. Can I somehow add condition that wont look at blank cells in column D?
