• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

Excel Formula - Add if three conditions are meet.

I have an Excel workbook with a tab named "EPAdjustments" and a tab named "2015 TrialBalance". I need a fomula in 2015 TrialBalance cell G14. The formula should return the sum of Column G in EPAAdjustments if 1) column B in EPAdjsutments is equal to 2015 TrialBalance cell $F$2 2) EPAAdjustments column F is equal to 2015 TrialBalance Cell $A$14 and 3)  the name in EPAdjustments column C is equal to the name in 2015 TrialBalance cell G56. Do you have a formula that can do this? Attached is a sample of the data.

Thank you.

Conernesto
SumIFS_2.xlsx
0
Conernesto
Asked:
Conernesto
  • 2
2 Solutions
 
ConernestoAuthor Commented:
I will be copying the formula across for other companies that meet the criteria on my 2015 TrialBalance.
0
 
Ejgil HedegaardCommented:
Use this in G14 and copy across
=SUMPRODUCT(SUBSTITUTE(EPAdjustments!$G$2:$G$109,",","")*(EPAdjustments!$B$2:$B$109=TEXT($F$2,"0000"))*(EPAdjustments!$F$2:$F$109=$A$14)*(EPAdjustments!$C$2:$C$109=G56))

Open in new window

0
 
NorieVBA ExpertCommented:
There are some problems with your data, mainly that various columns, e.g. column B and G, on EP Adjustments are actually text.

You can convert those columns to 'real' numbers like this.

1 Select the column.

2 Goto Data>Text to columns...

3 Click Finish.

Once you've done that this formula should work.

=SUMIFS(EPAdjustments!$G2:$G200,EPAdjustments!$B2:$B200,'2015 TrialBalance'!$F$2,EPAdjustments!$F2:$F200,'2015 TrialBalance'!$A$14,EPAdjustments!$C2:$C200,H56)
0
 
ConernestoAuthor Commented:
Thank you very much for your help with this formula.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now