# SUMIFS of multiple columns in a range

1. ## SUMIFS of multiple columns in a range

Hello, it's my first time posting so I hope this is alright. I'm trying to get the sum from a table, of all the numbers in multiple columns (separated by columns with unnecessary information), also based on an IF check for a person's name in a column on the left. I'll post my formula below. It isn't working, could anyone help me understand why? Or suggest any better alternative? The range will become larger every week, so I want a formula that's not too long.

=SUMIFS('2018 WK1-26'!\$G:\$DG,'2018 WK1-26'!\$B:\$B,Summary!\$B16,'2018 WK1-26'!\$D\$7:\$DG\$7,"Amt")

What I was trying to do, was have it do a check on the names in one column, and then do a second check for the word "Amt" in the row with the column headers, and have it sum all of the cells under each Amt column for each name. I hope I'm explaining this adequately.

2. ## Re: SUMIFS of multiple columns in a range

=SUM('2018 WK1-26'!\$G:\$DG*('2018 WK1-26'!\$B:\$B=Summary!\$B16)*('2018 WK1-26'!\$D\$7:\$DG\$7="Amt")), CSE
PS referencing the whole columns is not quite effective

3. ## Re: SUMIFS of multiple columns in a range

Ok, so I tried the formula you gave me, but it's returning 0 for the total. I'm relatively new to Excel so could you explain what the * marks are in this formula? It's not multiplying anything is it?

Also, what would be a better option than referencing the whole column? is that just for the checks, or for the sum range as well?

4. ## Re: SUMIFS of multiple columns in a range

Hello Ruuroga and Welcome to Excel Forum.
In the attached sample file the formula used is: =SUMPRODUCT(('2018 Wk 1-26'!B2:B8=B2)*('2018 Wk 1-26'!C1:H1="Amt")*('2018 Wk 1-26'!C2:H8))
If that were translated to your file it might read: =SUMPRODUCT(('2018 Wk 1-26'!B:B=B16)*('2018 Wk 1-26'!D7:DG7="Amt")*('2018 Wk 1-26'!D:DG))
If that doesn't work for your file, please upload a small desensitized sample by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.

5. ## Re: SUMIFS of multiple columns in a range

Thank you so much for the reply!
I got busy with other projects at work so I haven't been able to get back to this one.

Your formula is written so clearly! Thank you!! I can actually understand where it's referencing. I will try it out and see if it works!

6. ## Re: SUMIFS of multiple columns in a range

You're Welcome and thank you for the generous feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

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.06354 seconds
• Memory Usage 9,014KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (6)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (6)postbit_legacy
• (6)postbit_onlinestatus
• (6)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