Excel Array Formula Series #7: Boolean Logic & Criteria

[MrBMukati]

Your videos are awesome!

We can change the array formula to avoid using the year column.

=SUM(($C$12:$C$438=$I13)*($E$1­2:$E$438=J$12)*(YEAR($B$12:$B$­438)=$I$12)*$D$12:$D$438)

[adiprenne2006]

it would be nice if would spoke not so fast . :)

[sartaj2304]

This was fun. I liked the pace and insight . Thank you very much.

[ExcelIsFun]

I am glad that you liked it!

[loveleijai]

Hello Mr. Excellsfun, I have a question. I'm trying to do this formula but in one of the sets I want it to sum if the cells in the range contain certain text. Not an exact match. When I do the formula, it returns an error. Here's the formula:

=SUM(($A$2:$A$10="*Neighborhoo­d*")*($B$2:$B$10="Yes")*($C$2:­$C$10="Tier 1"))

Here is the data set:

Neighborhood TYesNo

Focused CareYesNo

Neighborhood CNoNo

HoustonWorksYesYes

Please help. Thanks

[ExcelIsFun]

Try this using Ctrl + Shift + Enter:

=SUM(ISNUMBER(SEARCH(E3,A2:A10­))*(B2:B10=F3)*(C2:C10=G3))

or this using just Enter:

=SUMPRODUCT(ISNUMBER(SEARCH(E3­,A2:A10))*(B2:B10=F3)*(C2:C10=­G3))

or this using just Enter:

=SUMPRODUCT(--ISNUMBER(SEARCH(­E3,A2:A10)),--(B2:B10=F3),--(C­2:C10=G3))

[ExcelIsFun]

E3 contains 'Neighborhood'

F3 contains 'Yes'

G3 contains 'Tier1'

[ExcelIsFun]

Sorry, in the last formula I put criteria in cells. These formulas have criteria in formula:

=SUM(ISNUMBER(SEARCH("Neighbor­hood",A2:A10))*(B2:B10="Yes")*­(C2:C10="Tier1"))

or

=SUMPRODUCT(ISNUMBER(SEARCH("N­eighborhood",A2:A10))*(B2:B10=­"Yes")*(C2:C10="Tier1"))

or:

=SUMPRODUCT(--ISNUMBER(SEARCH(­"Neighborhood",A2:A10)),--(B2:­B10="Yes"),--(C2:C10="Tier1"))

[cs2sw]

Thanks for sharing this videos, very refreshing and entertaining. Thanks 5/5

[ExcelIsFun]

You are welcome!

[denisrapp]

Dear excelisfun,

I try to do the same trick but with "average" and "min" functions insted of SUM, it gives me the wrong answer, when I try MIN it gives me a "0" however there not a single zero in the range.

with average it gives me a much smaller rezult than the correct one (I guess the avg function takes those zeroes into account).

am I doing smth wrong? could you please help?

[ExcelIsFun]

Dear denisrapp,

Search for and try these videos:

Excel Magic Trick #145 p1 MEDIAN IF

Excel Magic Trick #145 p2 MODE IF

Excel Magic Trick #145 p3 AVERAGE IF before 2007 Excel

--excelisfun

[yessirsir]

your voice man, feels like you talking to 5 years old kids ...sigh

[ExcelIsFun]

Dear yessirsir,

Some people say that. But that is just the way I am. I can't really change it. My intention is to share Excel knowledge.

But if you don't like the voice, you don't have to watch the free videos!

--excelsifun

[chippendails]

How does it work when you count the numbers.

isn't it still 1*1*1???

[ExcelIsFun]

Dear chippendails,

No it is more like SUM({1,1,1}*{0,1,1}*{0,1,1}), which then evaluates to SUM(1*0*1,1*1*1,1*1*1) , which then evaluates to SUM(1,0,1) =2

--excelisfun

--excelisfun

[ExcelIsFun]

Dear chippendails,

The above arrays of 1s and 0s come from the TRUEs and FALSEs

from the ranges that are being checked to see if they match the criteria.

--excelisfun

[chippendails]

ok I got this, but how can it be that the numbers gets greater than 3 in some cells?

[ExcelIsFun]

Dear chippendails,

When I run formula evaluator on this formula (counting formula):

=SUM(($C$12:$C$438=$I13)*($E$1­2:$E$438=J$12)*($F$12:$F$438=$­I$12))

none of the individual numbers are > 1. When I do this formula for adding sales:

=SUM(($C$12:$C$438=$I13)*($E$1­2:$E$438=J$12)*($F$12:$F$438=$­I$12)*$D$12:$D$438)

because of the 4th range. then you get something like this:

1*300+0*200+1*35 = 335.

The TRUEs & FALSEs (0s & 1s) never get bigger than 1.

--excelisfun

[chippendails]

Hey,

When you press ctrl+shif+down arrow.

how do you go bck to the cell with the formula you work with?

[ExcelIsFun]

Dear chippendails,

You can use the scroll bar, or if you want the cells locked (Absolute) you can hit the F4 key.

--excelisfun

[rmccallen]

Mike you are far too modest, your video's and samples files are nothing less than fantastic. I am a huge fan of Mr. Excel but I am also a huge fan of Mike Girvin. Please keep up the great work, you are teaching us wannabe experts some really great stuff.

Roger P McCallen

[ExcelIsFun]

Dear rmccallen,

I am glad that the videos and files are useful to you! I will keep making videos, but only because it is just too much fun!

By The Way, if you like this video, check out the Playlist named "Excel Formula Efficiency Series", it shows some great alternatives to the formulas in this video!

--excelisfun

[rmccallen]

Ha, I have already worked my through a bunch of these since yesterday.

Thanks!

[ExcelIsFun]

Dear rmccallen,

EXCELlent!!!!

--excelisfun

[everett99]

This series really is fabulous!!!! Words can't describe how impressed I am by what you've put out here. You've taken an incredible amount of time to walk folks through things they would never think to ask on the Mr Excel website. This is free online education for anyone with a desire to learn, and if anyone finds this and goes through it, they'll be one of the best Excel users anywhere they go. Jack from "Tropic Thunder" would say #12 MMakes me happy. You are a Mr Excel MVP.

[ExcelIsFun]

Dear everett99,

Thanks!

I am glad that the videos help!

I may take the time to make videos & explain things slowly & show the logic (which is important), but as far as being a Mr Excel MVP, no. Why? Cuz, if you hang out at the Mr Excel Message Board, you know that the people there know 100 times what I know. People like Aladin & Houdini & the VBA masters blow my mind! When I have a question, that is where I go; & I am always amazed at the elegant Excel solution!

Go Team!

--excelisfun

[ExcelIsFun]

Dear everett99,

BTW, If you know people who have the desire to learn, tell them about the Series here like:

Excel Basics

Name Series

Lookup Series

and of course the Array Series.

and remember, Ill keep making videos if you keep watching and rating!

--excelisfun

[cidfidoutube]

Hi Excellsfun

Another question for you. ;-)

How do u create a field in a datavalidation that would take all the data into consideration? In the above video, it would b all the sales since 2006 years (cells I12)

Cheers

[ExcelIsFun]

Dear ,

I guess so. If I understand what you are asking. You would use Data Validation - List and then highlight the whole column of sales data.

But I guess I don't really understand what you are asking.

--excelisfun

[libertarianPinoy]

Very nice and very helpful. I like the fact you try to explain the "inner workings" of Excel. Thank you very much for posting this.

However, I personally feel this could be presented in less time. But other than that great post.

I wanted to ask you about your date/timekeeping vid, but comment was disabled :(

[ExcelIsFun]

Dear libertarianPinoy,

Yes, if you know the "inner workings: of Excel, then it is easier to apply the lesson when trying to solve problems in the future.

Send me an e-mail if you have a question!

--excelisfun

[kennis942]

Dear Excel God

Is there any way that i can contact you with IM?(Instant Messenger) MSN,skype,whatever? Regards Milo. God of Qestions.

[ExcelIsFun]

Dear kennis942,

No god here. The Excel gods are at MrExcel Discussion Board.

No IM and all that. I just do this for fun. Making videos and posting them is like vacation to me. Otherwise, I have too much to do as it is.

You can ask basic questions. But if you want real Excel Gods to answer your questions, post them at MrExcel Discussion Board. This is where I go when I have questions.

--ExcelIsFun