# Need a sumifs with an "or" in one of the criteria...??

1. ## Need a sumifs with an "or" in one of the criteria...??

Guys; This seems like it should be simple but I'm stumped nevertheless.

I attached the data.

Note the red cell near the top left. What I have done is created a sumifs formula to basically say "sum the total in column J if the name in column B is "mrobledo", if the code in column T includes "CYO" and if the value in column P is blank."

All I need is for this formula to be modified so that it says:

"sum the total in column J if the name in column B is "mrobledo", if the code in column T includes "CYO" and if the value in column P is blank or includes the word "New."

That's it. The formula is right....all I need is for the criteria in column P to sum if it is blank OR includes the word "New."

Thanks for any help. I've been trying brackets and all kinds of logic, but can't make anything work. Thanks again.

2. ## Re: Need a sumifs with an "or" in one of the criteria...??

This should do it if I understand you correctly:

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

3. ## Re: Need a sumifs with an "or" in one of the criteria...??

Try this...

=SUM(SUMIFS(J:J,T:T,"CYO*",B:B,"mrobledo",P:P,{"","New"}))

4. ## Re: Need a sumifs with an "or" in one of the criteria...??

This could work for you too: -----nope!! Doesn't

Array Enter the following (Ctrl + Shift + Enter)

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

5. ## Re: Need a sumifs with an "or" in one of the criteria...??

An alternative way:
Make use of filter and subtotal formula

6. ## Re: Need a sumifs with an "or" in one of the criteria...??

Originally Posted by newdoverman

Array Enter the following (Ctrl + Shift + Enter)

=SUMIFS(J:J,T:T,"CYO*",B:B,"mrobledo",P:P,{"","New"})
That doesn't work for me.

..........B............P..........J..........T......
1..mrobledo....New.......10......CYO xx
2..mrobledo.................20......CYO xx

P2 = an empty cell

Array entered:

=SUMIFS(J:J,T:T,"CYO*",B:B,"mrobledo",P:P,{"","New"})

Returns 20

The correct result should be 30.

=SUM(SUMIFS(J:J,T:T,"CYO*",B:B,"mrobledo",P:P,{"","New"}))

Correctly returns 30.

7. ## Re: Need a sumifs with an "or" in one of the criteria...??

@ Tony thanks, just verified that myself.

This, I have tested and it, in my sleepy haze, seems to work fine

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

8. ## Re: Need a sumifs with an "or" in one of the criteria...??

Yeah, that'll work...

But, the SUM/SUMIFS version is much more efficient!

9. ## Re: Need a sumifs with an "or" in one of the criteria...??

Just giving alternatives.

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.07086 seconds
• Memory Usage 9,162KB
• Queries Executed 16 (?)
Template Usage (35):
• (1)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (9)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (2)postbit_attachment
• (9)postbit_legacy
• (9)postbit_onlinestatus
• (9)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_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete