Sumifs/Sumproduct with left

[Translationguy]

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!

 

[Michael M]

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]

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.

Let me know if you need more information!

 

[Michael M]

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]

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]

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]

Without typing everything, perhaps this can get you started:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	GuildName			TransactionType				itemMoneyCount			Guild	Type	goldsum
2	A			deposit				10000g0s0c			A	withdrawal	6055
3	C			deposit				10000g0s0c
4	B			deposit				40000g0s0c
5	C			withdrawal				500g0s0c
6	C			withdrawal				752g0s0c
7	A			withdrawal				6000g0s0c
8	A			deposit				700g0s0c
9	B			deposit				200g0s0c
10	A			deposit				400g0s0c
11	A			withdrawal				55g5s9c
12	C			deposit				250g0s0c
13
14
2

Cell Formulas
Range	Formula
M2	M2	=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]

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]

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.

Glad we could help!