Google Sheet; sumproduct + search

[dracula cyrus]

I am trying to sum all the numbers from a different column...
E.g. I got columns A, B, C, D, E, F, G, H, I, J, K

I would like to search for a name within a cell "e.g. Mary" but the cell includes other words or numbers too.. But I just want to capture the cell as long as it has the word "Mary".

I want to ignore A, D, G.. And capture the name in B, E, H.. and taking the column on the right (C, F, I) by summing them all together..

Please help me out. Been trying to find this codes for days but its seems too difficult or there isn't even one.

FYI, I using this for the calendar. Just to keep track of certain records easier.

 

[jasonb75]

You might need to post a visual example for clarity, from your post all I can see is

=SUMIFS(C:I,B:H,"*Mary*")

 

[dracula cyrus]

You might need to post a visual example for clarity, from your post all I can see is

=SUMIFS(C:I,B:H,"*Mary*")

Thank you for your quick response!!! Was busy the past few days..
Yes, this is an example.. I am trying to count each person's amount at the end of the month by adding the value in the following cell.
So e.g. I need to find all the cells that have "Garrett" and sum all the values under Garrett's name in the summary for the month.

However, I want the code to ignore the timing because I often key the timing beside the time for easy tracking for the day.

Do you happen to know any codes that would resolve this?

 

[dracula cyrus]

Hi all,

I've been trying to find a code to save my time from the calculation. This would save me plenty of time and hope anyone out there would assist me with this.

In the attached picture.. I am trying to search for a specific name from each date WHILE ignoring the time and match to those names in $W:$W. After which, I'll want to take the value in the cell beside and sum them in cell X, right beside the specific name.

For example.. Looking into ($K:$K, $N:$N, $Q:$Q, $T:$T = W6), where W6 is Garrett. I would want to find all the cells that have the word 'Garrett' but ignore other details within the same cell and take the value on the right to sum it up in X6. This is why Garrett is $60 for the date between 4-7 Jan.

 

[jasonb75]

Based on the screen capture, the formula that I suggested should work. I notice that you have merged some columns in your sheet which is a bad way to make a mess of things. Merging is fine for anything that you don't need formulas to look at for information, but for anything that will be used by a formula it is better to use a single column and make it wider. Merging affects different types of formulas in different ways but overall it is a bad habit to avoid.

I've assumed that the name "Kayden" in the summary is in W4, if not you will need to adjust the formula to fit.

=SUMIFS(M:V,K:T,W4&"*")

BTW you are looking for a formula, not 'code'. They are 2 completely different things.

 

[dracula cyrus]

Based on the screen capture, the formula that I suggested should work. I notice that you have merged some columns in your sheet which is a bad way to make a mess of things. Merging is fine for anything that you don't need formulas to look at for information, but for anything that will be used by a formula it is better to use a single column and make it wider. Merging affects different types of formulas in different ways but overall it is a bad habit to avoid.

I've assumed that the name "Kayden" in the summary is in W4, if not you will need to adjust the formula to fit.

=SUMIFS(M:V,K:T,W4&"*")

BTW you are looking for a formula, not 'code'. They are 2 completely different things.

Awesome! Thank you for your prompt reply once again!!

Yep, initially, I only can calculate if time and names are separated. I merge the cells together and put both time and name together and just try to out the formula. But yea, I get what ya mean!! thanks a lot!!