"Or" in a multiple criteria ... search?

Jennifre

Board Regular
Joined
Jan 11, 2011
Messages
160
Is there a way I can add an "or" into this formula?

=SUMPRODUCT(--(BE2:BE1364&""="4"),--(BN2:BN1364&""="1"))

I'd like to be able to tally the entries in BE which equal 4s or 3s + entries in BN which are 1s [and possibly other columns and entries)

Also, if anyone feels like letting me know [how these formulas really work, and how I can learn them better], I'm curious to know what the --s indicate in this formula?

Thanks!!!!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Why are you using &"" and quotes around the numbers?

Try:

=SUMPRODUCT(--((BE2:BE1364=4)+(BE2:BE1364=3)),--(BN2:BN1364=1))

For the -- operator, go here for a detailed explanation.
 
Last edited:
Upvote 0
Try

=SUMPRODUCT(--ISNUMBER(MATCH(BE2:BE1364,{3,4})),--(BN2:BN1364=1))

The -- convert an array of False/True to an array of 0/1 that SUMPRODUCT can operate on.
 
Upvote 0

Forum statistics

Threads
1,224,566
Messages
6,179,553
Members
452,928
Latest member
101blockchains

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top