IF statement clause and SUMIF statement formula help!!!!

**djmatok** · 10-27-2015, 05:18 PM

I have a set of earnings data with Main payee accounts that have sub accounts, now I am trying to pull earnings either by payee account or subaccount depending on the clients that I'm searching. Please take a look at my attachment. I'm already using the following formula but I want to be able to calculate when I have to pick up the earnings by payee account but minus out the client under that payee account that I have already picked up. =IF(A15=B15,SUMIF($A$2:$A$8,A15,$C$2:$C$8),IF(A15<>B15,SUMIF($B$2:$B$8,B15,$C$2:$C$8)))

**Sam Capricci** · 10-27-2015, 05:40 PM

can you explain your issue better? Your attachment shows that the ideal result would be 8874.37 but that appears to be the value for sub account 9964 though your formula seems to be returning the correct value for sub account 9963?
what should the result be?

**djmatok** · 10-27-2015, 05:43 PM

The spreadsheet that I am populating with new earnings has Sub accounts that for the current period had -0- earnings.
I need to formula to pick up sub-accounts that don't have earnings as follows
Total Payee account - Sub-account earnings already picked up = Allocate these earnings to the subaccount that has no earnings for the period as a default.

**oeldere** · 10-27-2015, 05:47 PM

d2 =if(countif($A$2:$A7,$A2)=countif($A$2:$A2,$A2),"yes","no") and drag down.

After that a pivot table.

See the attached file.

**djmatok** · 10-27-2015, 05:54 PM

This is not what I am looking for. I'm working with more than a million lines and I'm trying to have one formula for all my worksheet tabs doing the search.

**Sam Capricci** · 10-27-2015, 05:58 PM

is it ok to use a helper column?

**djmatok** · 10-27-2015, 06:00 PM

ok . If the Sub-account is crossed = Yes ; if it's not crossed into a Payee account = No.

**CARACALLA** · 10-27-2015, 06:08 PM

Hi

C14=IF(SUMIFS($C$2:$C$7,$A$14:$A$19,$A14,$B$14:$B$19,B14)=0,SUMIFS($C$2:$C$7,$A$14:$A$19,A2,$B$14:$B$19,B2),SUMIFS($C$2:$C$7,$A$14:$A$19,$A14,$B$14:$B$19,B14)) copydown

**oeldere** · 10-27-2015, 06:09 PM

In #5

Please Login or Register  to view this content.

1) Since you get answers of several forummembers it is always usefull to add to who you are replying.

one formula for all my worksheet tabs

2) you have to explain better what you want to achieve.

**djmatok** · 10-27-2015, 06:15 PM

oops! Sorry for not responding directly to the corresponding respondents.

**djmatok** · 10-27-2015, 06:17 PM

Hi Caracalla, the formula you suggested doesnt work I get a value error and circular reference.

**oeldere** · 10-27-2015, 06:17 PM

Then I advice you to correct that and help the forummembers to help you.

**djmatok** · 10-27-2015, 06:20 PM

Ok. I did the following tweak to my formula . I added the "helper" now I am getting an N/A for the sub-account that has no earnings.

**djmatok** · 10-27-2015, 06:21 PM

If(a16="y",vlookup(c16,$c$2:$d$8,2,false),if(a16="n",sumif($b$2:$b$8,b16,$d$2:$d$8)))

**djmatok** · 10-27-2015, 06:24 PM

UPDATED File

IF(A14="Y",VLOOKUP(C14,$C$2:$D$8,2,FALSE),IF(A14="N",SUMIF($B$2:$B$8,B14,$D$2:$D$8)))

Now, how can I amend this formula to pick up the remaining earnings from the Payee account in this case account 9964 and pull it under 9963. I have alot of these scenarios on my work file.

**oeldere** · 10-27-2015, 06:53 PM

show the result in your sheet and refer to the related cells with the same color.

A good file, speaks more for itself than 1.000 words (at least for me).

**djmatok** · 10-28-2015, 09:40 AM

Can someone please help me find the most effective way to pull off this formula?

**djmatok** · 10-28-2015, 09:55 AM

Please see my most recent file attachment you will see what I need.

**oeldere** · 10-28-2015, 10:05 AM

Did that and it was not clear to me, otherwise I would not replied that way.

**djmatok** · 10-28-2015, 10:07 AM

I'm really stuck on this one. I need help.

**djmatok** · 10-28-2015, 10:10 AM

updated the file with the colored result that I need.

**oeldere** · 10-28-2015, 10:14 AM

See the answer in #4.

**djmatok** · 10-28-2015, 10:24 AM

that did not work

**oeldere** · 10-28-2015, 10:41 AM

In what way that did not work.

I think it was the answer you were looking for.

If not, not only for me, but also for the other forummembers, you have to explain / show (in an excelfile) the result.

Just that did not work is much to short and non-explaining.

You want help from forummembers.

Help yourself, by helping the forummembers.

**djmatok** · 10-28-2015, 10:48 AM

Oeldere I appreciate everyone's help but I provided already three workbooks. The last sheet that I attached it shows you the result that I want to obtain. What I'm trying to accomplish here is to pull the remaining earnings not allocated to the client that crosses into the main account that for the period doesnt have earnings. At the end of the day all these clients are just different earning buckets and all should fall under the same payee account.

**oeldere** · 10-28-2015, 11:00 AM

And that is in my opinion what I showed you in #4.

But since that is not the result, I don't get it, so I wish you luck finding your solution.

Maybe other forummembers do understand your question and can solve it.

**djmatok** · 10-28-2015, 02:53 PM

Here is the result that I need.

**oeldere** · 10-28-2015, 03:38 PM

Here is my solution.

**djmatok** · 10-28-2015, 04:48 PM

Thank you! But that is not the result that I need.

IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Re: IF statement clause and SUMIF statement formula help!!!!

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How do I pass a MID( statement through a IsNumber in an If, Then, Else clause?

[SOLVED] SQL Transform Statement in VBA - Using Variable in the FROM clause

[SOLVED] Formula for Income Statement in financial statement

[SOLVED] Help please, IF statement/SUMIF statement

Bookmarks

Bookmarks

Posting Permissions