# 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.

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
###### Who is Participating?

Distinguished 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))
``````
NominalList:
``````=Sheet1!\$E\$2:INDEX(Sheet1!\$E:\$E,MATCH(99^99,Sheet1!\$J:\$J,1))
``````
ValueList:
``````=Sheet1!\$J\$2:INDEX(Sheet1!\$J:\$J,MATCH(99^99,Sheet1!\$J:\$J,1))
``````
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)),"")
``````
Make sure you have Nominal & Account Column in Sheet2 as well.
Check in attached....
whfit_CountIfs_v1.xlsx
0

Commented:
since you got multiple criterion, try use SUMIFS instead of SUMIF, like:

``````=SUMIFS(G2:G4,E2:E4,C10,F2:F4,C11)
``````
29076899.xlsx
0

Author 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

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

or you may upload yours here?
0

Author 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

Distinguished 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)),"")
``````
29076899_v2.xlsx
0

Author 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.