SUMIFS formula to pull specific detail into already entered formula

[MNexcel]

Good evening,

I have been working for about a week to see if I could get this formula to work. Currently I do have a SUMIFS formula in the cell, but I also want to enter another set of criteria and can't think of how to do it.

What I'd like to add into the formula:

In AA (where you see names), I want to for instance pull the name of "Jeremy" into cell I2 if the chargeback amt column is greater than a negative 99.99. Currently, the formula is =(SUMIFS(M:M,F:F,"Electronic Check",V:V,""))*-1

Any and all help would be appreciated as I'm really blanking on this one. Let me know if you would need anything further.

 

[jasonb75]

Do you mean

=SUMIFS(M:M,F:F,"Electronic Check",V:V,"",N:N,"Jeremy,M:M,"<=-99.99")

You have referred to parts of the sheet that are not visible in the screen capture which means that we have to guess.

A good example would only contain data that is relevant to the question and would have the (correct) answers that you expect from the sample data typed in so that we have a target to aim for.

 

[MNexcel]

USE THIS AS DATA

Sorry about that @jasonb75 - you're right, I had to manipulate some of the data initially. Hopefully this helps.

So here is what is going on:

In cell I2, it is pulling in all the data from F that is labeled as "electronic check" and in column AI has a value not equal to blank, then it is populating the value in that cell. In cell J2, you can see it's pulling in a negative 242, this is a transaction for a client named St. Vrain Valley SD. Essentially, all I'd like to do within that formula is add to it where if the client name is "St. Vrain Valley SD"(from column AA) and column M is greater than -99.99, that it takes that value away from the total number in cell I2. For example, I2's total really should be 3,225.06, can be seen in K2.

That's what I'm looking for as the end result. Please let me know if I'm not being very clear or can do anything to help and apologies for the confusion.

 

[jasonb75]

Hi, sorry for delay in getting back to your thread, had a silly busy week and just been trying to catch up with all of the questions I've been responding to on here, I haven't had chance to look at yours again yet but hopefully will get it done tomorrow (Saturday) evening.

 

[jasonb75]

Hi, I tried to open your file to have a look at this but it is coming up with a message that I don't have approved access to the file, I don't use google sheets regularly so I'm not entirely familiar with how it works but I'm guessing that you have it set up so that only specific people can open it?

 

[MNexcel]

@jasonb75 - go ahead and try now, I made it so anyone could edit. Sorry about that.

 

[jasonb75]

Looks like you have the absolute value for St. Vrain in I4 so subtracting that from the curent result in I2 will give you the correct result (unless I'm missing something).

=(SUMIFS(M:M,F:F,"Electronic Check",AI:AI,"")*-1)-I4

 

[MNexcel]

That works like a charm, thanks so much! I can't believe I didn't think of that as a solution, but it's nice having people who can help point those out in these scenarios.

 

[jasonb75]

You're welcome

Sorry it took so long to get there, I just wasn't seeing it from the descriptions that you gave us earlier.

 

[MNexcel]

You're welcome

Sorry it took so long to get there, I just wasn't seeing it from the descriptions that you gave us earlier.

No worries at all

Yeah, it’s complex and my explanation wasn’t helping. Have a great Holidays!