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

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Business Systems Analyst , ex-Senior Application EngineerCommented:
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
Business Systems Analyst , ex-Senior Application EngineerCommented:
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
Excel & VBA ExpertCommented:
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
Excel & VBA ExpertCommented:
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

Experts Exchange Solution brought to you by