# Sum(if()) with multiple parameters

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

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

3. 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.``

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1