excel omits adjacent cells fix

[Magaletta]

How do i fix this formula from excel omits adjacent cells (note: it is on a excel table which prepopulates the formula but comes up with the error)

Formula: =SUMIFS(D$2:D2, B$2:B2, "<>credit", B$2:B2, "<>transfer")

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Magaletta]

I have made an excel table and add new entries each day.

However there are a few errors with the formulas and I am not sure why they arent staying consistant to what I had it set as (it keeps changing as per the highlighted ones in red).

Please help.

[AliGW]

OK - which entries do you think are incorrect? What should they be? You haven't annotated the workbook at all.

[AliGW]

I suspect you need one or other of these. Try them and let us know:

=SUMPRODUCT((B$2:B2<>"credit")+(B$2:B2<>"transfer"),D$2:D2)

or:

=SUMPRODUCT((B$2:B2<>"credit")*(B$2:B2<>"transfer"),D$2:D2)

[Magaletta]

G26:G34 (highlighted in red) the formula chnaged automatically which isnt consistant with the formula in G2:G25. Not sure why excel chnaged it - there is a error showing saying that it is inconsistant. I can manually chnage this however, I have set it up as a table so it automatically calculates as in the future this will have many rows.

H26:H34 - same issue as above

All in Column I has an error displaying excel omits adjacent cells

I do not know th efix to any of these to en sure they consistantly update with new entires of data

[AliGW]

You still haven't told us WHAT the results should be where they are wrong.

Have a look at my previous post - see if that gets you started. Until you provide expected results in the workbook, I shan't try anything else.

[AliGW]

he formula chnaged automatically which isnt consistant with the formula in G2:G25. Not sure why excel chnaged it

Have you been inserting and deleting rows???

[AliGW]

Ah! I think I see what's going on.

Try this in G2:

=SUMIF(B$2:[@Type],B2,D$2:[@Amount])

[Magaletta]

In G25 the formula is =SUMIF(B$2:B25,B25,D$2:D25) - this is correct
In G26 the formula is =SUMIF(B$2:B35,B26,D$2:D35) - this is incorrect and not sure why it automatically chnaged to this

In H25 the formula is =SUMIF(C$2:C25,C25,D$2:D25) - this is correct
In H26 the formula is =SUMIF(C$2:C35,C26,D$2:D35) - this is incorrect and not sure why it automatically chnaged to this

Data in column I is correct until row 29 where it chnages - this is incorrect and chnages because of the error in formula


I tried what you suggested and it comes up with the same error sorry

[Magaletta]

This worked for column G thanks

[AliGW]

OK - in the attached I've changed all formulae to structured table references - see if they work for you.

[Magaletta]

Ali, thank you so much this worked.

I appreciate your help and thanks for the tips regarding this thread!

[AliGW]

Glad to have helped.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.