Translationguy
New Member
- Joined
- Nov 4, 2016
- Messages
- 26
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!
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!