Count if multiple functions are true?

**eoindub** · 09-10-2014, 05:31 AM

Hi all,

I would appreciate any help from the Excel gurus here! I have an excel file from say, an airline, that lists passengers who went through the airport. Some of them have a frequent flyer card. Of those passengers who DO NOT have a frequent flyer card, I want to mark which of them have the same surname of those WITH a frequent flyer card who scanned on the same date, same flight and within 1 minute of those with the card (i.e. they would be family members). I only want to mark ONE family member. I need to do this with a formula and when I say mark just a "count" string in a separate column is fine.

This will ultimately be added into another formula. I've tried literally everything I can think of but I cannot get it to work.

I've attached a sample of the file. I've separated the surnames of those who have a card and those who don't have a card. Then I did an if statement where if the data in column J is not "C" (just a requirement), AND the count of the flight number in column E is greater than 1, then vlookup the names of those who don't have a card against the list of those who do. It's still not accurate enough. It needs to reflect the same date as the person with the card, and within 1 minute of that person scanning. At this stage my eyes are rolling in my head so I might not be thinking about it accurately!

ANY help is greatly appreciated. I need to get a formula in a single line and not have to rely on additional columns (beyond A-K). This is just part of the file so not every name will be visible but the very last entry should be a 100% match.

**:) Sixthsense :)** · 09-10-2014, 05:57 AM

Please show the expected result for every records and explain in the nearby cell how you arrived it. So that we can give you the exact solution

**eoindub** · 09-10-2014, 06:19 AM

Originally Posted by :) Sixthsense :)

Please show the expected result for every records and explain in the nearby cell how you arrived it. So that we can give you the exact solution

Hi Sixthsense,

Please see the attached with the information you requested. For clarity it I've pasted it here also:

The cells in this column should say "Count" if the surname from column H for those WITHOUT a Card matches the surname for those WITH a Card, where the flight ID in column E is the same AND the date in column A is the same AND the Entry Time in column B is within 1 minute of the person WITH the card.

This should all be in one formula without relying on any other columns after A-K.

You can see the formula for how you know a person has a card or not in column L. The formula for getting the surname is in column M.

If the above criteria is not true, the cell should say "No"

Help is greatly appreciated!

**romperstomper** · 09-10-2014, 06:59 AM

Try this in P2:

=IF(COUNTIFS(H:H,N2&"*",E:E,E2,B:B,">="&B2-TIME(0,1,0),B:B,"<="&B2+TIME(0,1,0)),"Count","No")

**Glenn Kennedy** · 09-10-2014, 07:55 AM

A really nice way of getting there from Rory. I had a much more cumbersome idea in mind. However, it needs one tweak (in my opinion) to meet the OP's requirement.

Formula:

Please Login or Register  to view this content.

This removes the need to use column N.

**romperstomper** · 09-10-2014, 08:15 AM

Ah yes, I missed the restriction on columns. I'd change it to:

=IF(C2="TEST Airways",IF(AND(LEFT(RIGHT(G2,24),2)="BA",LEFT(RIGHT(G2,13),1)<>"M"),"No",IF(COUNTIFS(H:H,LEFT(H2,FIND("/",H2)-1)&"*",E:E,E2,B:B,">="&B2-TIME(0,1,0),B:B,"<="&B2+TIME(0,1,0)),"Count","No")),"No")

**eoindub** · 09-10-2014, 10:08 AM

Many thanks guys! I modified rompers original formula into this:

=IF(COUNTIFS(H:H,IF(OR(AND(MID(G2,LEN(G2)-23,2)="BA",MID(G2,LEN(G2)-12,1)<>"M")),,LEFT(H2,FIND("/",H2)-1)&"*"),E:E,E2,B:B,">="&B2-TIME(0,1,0),B:B,"<="&B2+TIME(0,1,0)),"Count","No")

So as not to use any new columns

Thanks Romper

It seems to work - but when I test it by changing the times to +1 or 2 minutes or - 1 or two minutes in the "Entry Time" column, I'm not always getting the correct result - could you please test and see if it's just me?

**romperstomper** · 09-10-2014, 11:02 AM

Can you give a specific example?

**eoindub** · 09-10-2014, 11:06 AM

Originally Posted by romperstomper

Can you give a specific example?

For example in row 47 if I manually changed the time to say 06:55:27, "Count" still remains. Might be an excel specific thing though as when I copy a time value from another row, it changes?

**romperstomper** · 09-10-2014, 11:51 AM

I think the formula is wrong without the helper columns. Try this one, array entered with Ctrl+Shift+Enter, then filled down:

=IF(AND(LEFT(RIGHT(G2,24),2)="BA",LEFT(RIGHT(G2,13),1)<>"M"),"No",IF(SUMPRODUCT((LEFT($H$2:$H$47,FIND("/",H2)-1)=IF(LEFT(RIGHT($G$2:$G$47,24),2)="BA",IF(LEFT(RIGHT($G$2:$G$47,13),1)<>"M",LEFT(H2,FIND("/",H2)-1),"Z"),"Z"))*($E$2:$E$47=E2)*($B$2:$B$47>=(B2-TIME(0,1,0)))*($B$2:$B$47<=(B2+TIME(0,1,0))))>0,"Count","No"))

Count if multiple functions are true?

LinkBack

Thread Tools

Rate This Thread

Display

Count if multiple functions are true?

Re: Count if multiple functions are true?

Re: Count if multiple functions are true?

Re: Count if multiple functions are true?

Re: Count if multiple functions are true?

Re: Count if multiple functions are true?

Re: Count if multiple functions are true?

Re: Count if multiple functions are true?

Re: Count if multiple functions are true?

Re: Count if multiple functions are true?

Thread Information

Users Browsing this Thread

Similar Threads

Multiple IF(FIND Functions with Multiple "TRUE"

[SOLVED] Using a function to count the number of rows in which multiple values are true

[SOLVED] Count occurences of a given value in Col A with multiple conditions being true

How to return multiple values in multiple cells if argument is TRUE in IF functions?

Excel 2007 : Multiple Conditional Count Functions

Bookmarks

Bookmarks

Posting Permissions