# SUMIFS Multiple Columns

1. ## SUMIFS Multiple Columns

Hi I have the following formula which works fine for financials in AL...

=SUMIFS(AL:AL, \$Q:\$Q, "Y")

I have financials in AL and these will continually be added to.

I also have financials in AM and AN. Is it possible to add up all values in columns AL, AM and AN where Q = Y?

Or will I have to run the calculation 3 separate times and add up the result at the end?

2. ## Re: SUMIFS Multiple Columns

``Please Login or Register  to view this content.``

3. ## Re: SUMIFS Multiple Columns

Doesnt work at all if I use SUMIF

If I use SUMIFS I get a #VALUE! error

4. ## Re: SUMIFS Multiple Columns

Hello
I don't believe you can use a multi column sum range with SUMIF or SUMIFS. You will probably need to use SUMPRODUCT but this won't work when referencing entire columns as you're doing. It's not very efficient to do that. If you were to create dynamic ranges you could use something like.

Formula:
`Please Login or Register  to view this content.`

Here 'Criteria' and 'Data' would be dynamic named ranges.

DBY

5. ## Re: SUMIFS Multiple Columns

Originally Posted by DBY
Hello
I don't believe you can use a multi column sum range with SUMIF or SUMIFS. You will probably need to use SUMPRODUCT but this won't work when referencing entire columns as you're doing. It's not very efficient to do that. If you were to create dynamic ranges you could use something like.

Formula:
`Please Login or Register  to view this content.`

Here 'Criteria' and 'Data' would be dynamic named ranges.

DBY
You should be able to, attached is a working example. I think my original formula had the criteria and ranges wrong way round.

Should be:
``Please Login or Register  to view this content.``

6. ## Re: SUMIFS Multiple Columns

@pjwhitfield

Have you manually checked your results?

Although it does not error, SUMIF cannot operate correctly with a sum_range of a different dimension to that of the range. In such cases, it reduces the larger of the two such that it is of an equal size to the smaller.

=SUMIF(D:D,"a",A:C)

is equivalent to:

=SUMIF(D:D,"a",A:A)

i.e. is not taking into consideration columns B and C at all for the sum, which can easily be manually verified.

Regards

7. ## Re: SUMIFS Multiple Columns

@pj

I have looked at your example and as XOR confirms it's only summing column A not A:D the answer with Sumproduct is 3657 not 3227 from the Sumif, which is the total of column A. It's deceptive because the two figures are relatively close in the 3000's range.

DBY

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

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.06623 seconds
• Memory Usage 9,078KB
• Queries Executed 16 (?)
Template Usage (36):
• (2)bbcode_code
• (1)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (7)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (7)postbit_legacy
• (7)postbit_onlinestatus
• (7)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (49):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• postbit_attachment
• tag_fetchbit_complete