Excel Formula based on multiple criteria

Hi

I'm trying to create a formula that will look at two columns and then add up the 3rd column if both criteria are met.

Screenshot of Excel sheet
I've used SUMIF before which has always worked but I'm unable to get this working.

What I would like to do is for Excel to look at Column E for CFT1000, then if Column G matches FPUP01 it then adds all values in Column J together.

Thanks
whfitAsked:
Who is Participating?
 
ShumsDistinguished Expert - 2017Commented:
First Create 3 Named Range in Sheet1 with below formulas:
AccountList:
=Sheet1!$G$2:INDEX(Sheet1!$G:$G,MATCH(99^99,Sheet1!$J:$J,1))

Open in new window

NominalList:
=Sheet1!$E$2:INDEX(Sheet1!$E:$E,MATCH(99^99,Sheet1!$J:$J,1))

Open in new window

ValueList:
=Sheet1!$J$2:INDEX(Sheet1!$J:$J,MATCH(99^99,Sheet1!$J:$J,1))

Open in new window

Then in Sheet2 where you want Sum Formula to be, enter below formula in second row and drag down:
=IF(COUNTIF($G$2:$G2,$G2)=1,SUMPRODUCT(SUMIFS(ValueList,NominalList,$E2,AccountList,$G2)),"")

Open in new window

Make sure you have Nominal & Account Column in Sheet2 as well.
Check in attached....
whfit_CountIfs_v1.xlsx
0
 
Ryan ChongCommented:
since you got multiple criterion, try use SUMIFS instead of SUMIF, like:

=SUMIFS(G2:G4,E2:E4,C10,F2:F4,C11)

Open in new window

29076899.xlsx
0
 
whfitAuthor Commented:
Thanks for the reply Ryan but I can't seem to get that working either, could you use my example and show me the formula I would need to use?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ryan ChongCommented:
where's your example? I have provided an example based on your screenshot.

or you may upload yours here?
0
 
whfitAuthor Commented:
Sorry I didn't see the attached example. That makes sense but can you have a formula that looks for text like SUMIF does?
0
 
ShumsDistinguished Expert - 2017Commented:
Ryan,

I think OP wants sum only in first occurrence of Account:
If you want formula in Col K, then try below formula in K2 and drag down:
=IF(COUNTIF($G$2:$G2,$G2)=1,SUMPRODUCT(SUMIFS($J:$J,$E:$E,$E2,$G:$G,$G2)),"")

Open in new window

29076899_v2.xlsx
0
 
whfitAuthor Commented:
Thanks Shums. That works but could I ask what would it look like if i needed the calculation on sheet 2? And if there were 20,000 rows?
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.

All Courses

From novice to tech pro — start learning today.