EXCEL COUNTIF - UNLESS or AND 4

[susejdrol]

Team,

Is there a way to add an UNLESS or AND clause to a countif. For example: Let's say you have two columns, one has group assignment and the other has a break time.

I want to either count everyone whose break time is "X" and is a member of group "A", or everyone whose break time is "X" and is not in group "A". Where X is any time I enter and A is a group letter.

Either solution will work since everyone is either in group A or group B, so it is a wash whether I count A's or "Not B"'s.

Currently what I have is:
=COUNTIF($AO$3:$AO$263,"="&BA$1)

Where the column AO holds break times and cell BA1 holds the time I want to compare it to. THe piece that I am missing is the group piece.

The reason there is no $ on BA is that times are actually stored in AU1 thru DF1 and leaving it off allows me to drag across.

Thanks

CP

 

[Dreamboat]

Believe it or not:

=SUM((AO3:AO263="a&quot*(B3:B263="x&quot)

When you're doing typing this formula, you must hit Ctrl+Shift+Enter because it's an array formula.


Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[KenWright]

Or you could use SUMPRODUCT in which case you wouldn't need to array enter it.

=SUMPRODUCT((AO3:AO263="A&quot*(B3:B263="X&quot)

and

=SUMPRODUCT((AO3:AO263=&quot;A&quot*(B3:B263<>&quot;X&quot) for the count of all Non A's


Regards
Ken.............

 

[susejdrol]

You know. It never even occured to me to try that.

Thanks a lot guys.

CP

 

[Dreamboat]

Why should it? It's lame if you ask me. Why not a special countif? I could never figure that one out.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[Zathras]

When the selection logic becomes complex, it's time to learn about database formulas.
To simulate this case, set up a worksheet like this:
[blue]

A1: 'Group
A2: 'A
A3: 'B
A4: 'A
A5: 'B
A6: 'A
A7: 'B
A8: 'A
A9: 'B
A10: 'A
B1: 'Break Time
B2: 11:00
B3: 11:00
B4: 11:30
B5: 11:30
B6: 11:30
B7: 11:30
B8: 12:00
B9: 12:00
B10: 12:00
E1: 'Group
E2: 'A
F1: 'Break Time
F2: 11:30

[/color]
Assign range names &quot;Database&quot; to A1:B10 and &quot;Criteria&quot; to E1:F2. (This step is optional, but it makes everything else easier.)
Enter this formula in any available cell:
[blue]

  =DCOUNTA(Database,1,Criteria)

[/color]
Compare the simplicity of that formula combined with the entries in E2 and F2 with the equivalent formulas as suggested above:

  {=SUM((A2:A10="A")*(B2:B10=TIMEVALUE("11:30:00")))}

and

  =SUMPRODUCT((A2:A10="A")*(B2:B10=TIMEVALUE("11:30")))

 

[Dreamboat]

But I hate D functions! LOL

Thanks, Zathras. I guess what I mean is trying to find a COUNT and having no idea that they'd use something besides COUNT or COUNTIF. That's been my real problem with it...

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[lespaul]

I have used the D functions alot (with Dale123321's help) and find that they are very powerful and can do very complex calculations. For instance, I have to report on the number and types of cases an officer is assigned. Since LotusNotes is not very reporting friendly I export all pertinent information to a DATA sheet in my workbook and can then perform the calculations that I need to report.

As I become more accustomed to how the functions work, I can find many more applications for the Database functions in Excel.

Zathras gets a star on me!



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!