Sumifs/Sumproduct with left

Translationguy

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

Some videos you may like

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
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Nov 4, 2016
Messages
21
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!
 

Attachments

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Nov 4, 2016
Messages
21

ADVERTISEMENT

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
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,770

ADVERTISEMENT

Without typing everything, perhaps this can get you started:

Book1
ABCDEFGHIJKLM
1GuildNameTransactionTypeitemMoneyCountGuildTypegoldsum
2Adeposit10000g0s0cAwithdrawal6055
3Cdeposit10000g0s0c
4Bdeposit40000g0s0c
5Cwithdrawal500g0s0c
6Cwithdrawal752g0s0c
7Awithdrawal6000g0s0c
8Adeposit700g0s0c
9Bdeposit200g0s0c
10Adeposit400g0s0c
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
Joined
Nov 4, 2016
Messages
21
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
Joined
Aug 18, 2015
Messages
10,770
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top