So, Im currently trying to make a google sheet where I can Input Data, and then calculate points based on different criteria of the Input. The relevant Part of the Input Data would look something like this:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Apple, Banana, Orange | something else | 4 | Fig, Grapefruit | something else | 2 |
| Orange, Fig, Grapefruit | something else | 3 | Banana | something else | 1 |
Now what I want to do is for example check all cells in the column A and D for 'Banana' and then average the points in C and F for which the cell in the same row contains banana. The Cells in B and E contains other data that I will use, but it will sort itself out once I manage to do this.
Obviously I already checked the Internet for this solution. I tried using regexmatch, search and find, and all of them didnt work as intended.
Right now I have: =IFERROR(ArrayFormula(SUM(IF(A:D = "Banana";C:F)))/COUNTIF(A:B; "Banana");-1)
This does not work though, in this example it would just output 1, but I want it to say 2,5. Surrounding the word with * did not work, it just outputs 0. It works when I just want to check for a word using *, but when I also let it add the numbers, it doesnt anymore, and I dont know why.
I would therefore like to know how I would solve this, because I tried everything I could think of.
CodePudding user response：
Try
=query({A:C;D:F};"select AVG(Col3) where Col1 like '