Sum(if()) with multiple parameters

I am attempting to aggregate values from a worksheet table and I need to use criteria from three columns in this array. Typically, if I wanted to return a total from Column G based on some value in Column A :

Column A named range "PCLAS"; Column G named range "VALUE" - then

Please Login or Register  to view this content.

Is there a way to use multiple criteria to SUM/COUNT/IF criteria from more than one column is met?

TIA

You can extend the formula you already have but it would probably be easier to use SUMPRODUCT which doesn't require CTRL+SHIFT+ENTER, e.g. if you want to add a criteria for range XYZ to be = "Something" you'd use

=SUMPRODUCT(--(PCLAS = "550"),--(XYZ="Something"),VALUE)

You can add extra criteria in the same way

Note: typically you wouldn't use quotes around numbers.....unless they're stored as text

Are the '--' to be included? I looked in help and SUMPRODUCT information there is not very clear.

Anyway I tried it both ways and still get '#REF!' return. I am using named ranges and they are all of the same length, etc.

Please Login or Register  to view this content.

Yes, you need to use the --s. Look here for more information on SUMPRODUCT, various uses and syntaxes etc.

Formula 3 looks like it should do what you want, i.e.

=SUMPRODUCT(--(t2_tca=$B6),--(t2_pgroup="D"),(t2_acres))

If you get #REF! that might be because you have #REF! errors in the referenced ranges....

Yes, you need to use the --s. Look here for more information on SUMPRODUCT, various uses and syntaxes etc.

Formula 3 looks like it should do what you want, i.e.

=SUMPRODUCT(--(t2_tca=$B6),--(t2_pgroup="D"),(t2_acres))

If you get #REF! that might be because you have #REF! errors in the referenced ranges....

Yes that was the case. I have spoken with our DBA about trimming varchar fields and using COALESCE to eliminate nulls from data. Actually, I resolved the issue by creating a function that concatenated the necessary data from the three columns. This created a single column eliminating the need for multiple criteria in SUMIF/SUMPRODUCT and the trimming/null value issues.

Thanks