# Sumifs/Sumproduct with left

#### Translationguy

##### New Member
Hi All

I'm quite new to sumproduct but wondering if you can help.

I play a game with a guild where people contribute items and money etc and I'm trying to log it to show what is owed after we make/use items. The vast majority of it is done with exports etc as I can get a csv dump of what has been deposited/withdrawn.

My main issue now is that I'm trying to sum up all of the money people have withdrawn and deposited against each person, however this is listed as gold such as 752g50s10c, so doesn't work as a number.

Using the above example, I can use =LEFT(H594,(FIND("g",H594,1)-1)) to return 752 (no need to worry about the smaller units), however I'm not having much luck with incorporating these details into a more extensive formula.

Sheets:
Guild Bank Log
C - States whether it was money or an item
D - States whether it was a deposit or withdrawal
E - States who
H - States the amount in 00g00s00c format

I originally wrote the following but found that you can't do sumifs with left?
=sumifs(left('Guild Bank Log'!H:H,(FIND("g",'Guild Bank Log'!H:H,1)-1)),'Guild Bank Log'!E:E,A3,'Guild Bank Log'!D:D,"Deposit")

I then tried this but think my knowledge is lacking to get it actually working
=SUMPRODUCT(--('Guild Bank Log'!E:E=Sheet2!A3),(LEFT('Guild Bank Log'!H:H,(FIND("g",'Guild Bank Log'!H:H,1)-1))))

If someone could help with this it would be appreciated! Trying to get this working with as little maintenance as possible so that the guild can make items for the entire guild and save everyone gold!

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Michael M

##### Well-known Member
A small cleaned sample of your data would help us understand a little better ??
You can use the XL2BB in my signature block to upload your sample

#### Translationguy

##### New Member
Hi

Thanks for your reply, I'm mostly working on Sheets but have been using Excel for testing purposes, but yes most of the data is in Sheets. Attached in an image showing the guild bank log worksheet which is being referenced.

#### Attachments

• guild bank log.png
101 KB · Views: 6

#### Michael M

##### Well-known Member
An image is not something we can work with and I'm willing to bet most posters won't want tp retype all of your date, to test it...

#### Translationguy

##### New Member

No problem, I thought it was more a visualisation thing, here's a link to a cut down version of the sheet: Raid consumables question

Those formulas are intended to be put into Raider Metrics columns Q and AD.

Hope this helps!

#### Michael M

##### Well-known Member
Have you considered using a helper column to extract the "gold" ....
AND
Alos consider a Pivot table for the rest of the data

#### Eric W

##### MrExcel MVP

Without typing everything, perhaps this can get you started:

Book1
ABCDEFGHIJKLM
1GuildNameTransactionTypeitemMoneyCountGuildTypegoldsum
3Cdeposit10000g0s0c
4Bdeposit40000g0s0c
5Cwithdrawal500g0s0c
6Cwithdrawal752g0s0c
7Awithdrawal6000g0s0c
9Bdeposit200g0s0c
11Awithdrawal55g5s9c
12Cdeposit250g0s0c
13
14
2
Cell Formulas
RangeFormula
M2M2=SUMPRODUCT(--(\$A\$2:\$A\$15=K2),--(\$D\$2:\$D\$15=L2),--("0"&REPLACE(\$H\$2:\$H\$15,SEARCH("g",\$H\$2:\$H\$15&"g"),9,"")))

With SUMPRODUCT, you should include a maximum row in the ranges, and not use whole column references for performance reasons.

#### Translationguy

##### New Member
Awesome thank you very much! I had a look at what you did and putting it into context for me helped me to realise how simple the base sumproduct formula is, I'll need to play around with some of the other parts like the search and replace aspects.

In terms of feedback on the formula, it almost worked however I found that it was capturing the non-monetary deposits/withdrawals as the quantity of these are entered into the same column, though without the g/s/c etc.

I entered another argument into it which looks at column "C", items will be deposited into specific tabs of the bank and the column will show which, but money will be entered with a neutral "Money" status and now it works perfectly!

We'll test this out for a few months and see how it goes. If all goes well then we may actually be able to get someone to turn this into an addon for the masses

Many thanks once again!

#### Eric W

##### MrExcel MVP
Yes, the basic concept of SUMPRODUCT is pretty simple. The 3rd term I used was a bit tricky, since it had to extract the gold value, and work on empty columns. But I'm sure you can figure it out with help from the Evaluate Formula tool. Good job adding your column C term! Let us know if you have any other questions.

Replies
1
Views
233
Replies
3
Views
111
Replies
4
Views
292
Replies
2
Views
155
Replies
3
Views
283

1,127,870
Messages
5,627,362
Members
416,245
Latest member
Xterminat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back