Is there a Substitute to the AND Function

volley_boy1

New Member
Joined
Nov 23, 2009
Messages
9
Hello,

I have a formula where I'm trying to verify if 2 conditions are true, and if so, pull the info from the row where both of those conditions are met.

The problem is that both conditions I want the formula to verify are text and the AND fuction tests for logical values (and text apparently isn't a logical value).

So anyways, is there someway I can substitute the AND fuction for a different function but acheive the same result (verify that both conditions are true, if so, pull the value from the line that it's correct)??

Thank you for your help!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
And will work on text values, you just have to make it a logical

=IF(AND(A1="text1",B1="text2"),C1,"")

A1="text1" <--this is now a logical with True or False answer. Is A1 = "text1"
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the board!!
The AND function could care less if you are testing numbers or text. Perhaps you can post the formula you tried, the expected results, and some sample data!!

lenze
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Can you post some sample data, the test you want to perform, and the
results you want calculated? Also post the formula you've tried (even if it
doesn't work). That'll help us understand your issue.
 

volley_boy1

New Member
Joined
Nov 23, 2009
Messages
9

ADVERTISEMENT

Wow.... thanks for everyone's quick replies.

The concept is The following:

- Verify if a specific time (in this case "8:30 - 9:15 AM) shows up anywhere in cell range A28:34.
- Check if wherever it finds that criteria to be true, then verify if cell range C28:34 has "General Shallow" (this would have to be in the same row).
- Where both of those conditions are TRUE, then take the entered attendance in cell G28:34 (again from whatever row both of these conditions are TRUE).

There's 31 sheets in the work book (numbered from 1 to 31 - for each day of the month) and this formula would be on the Totals page at the end.

Here'e the formula I started off with. It works for testing 1 criteria but I haven't been able to succesfully modify it to test both conditions.

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!A28:A34"),"8:30 - 9:15 AM",INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!G28:J34")))

This takes the attendance from any cell in the A28:34 range from all 31 sheets (that has 8:30 - 9:15 AM) and adds them together to give me a total for the month.

I just need it to be more specific and look for the specific class name as well.

Hope this is enough info.

Thanks again to everyone for your help!

Here'e the formula I started off with. It works for testing 1 criteria.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Do you really need 31 sheets??? If the day is the ONLY difference, it might be best to have only one sheet with a Date column. Now you can get what you need with a simple Pivot Table and NO Formulas

lenze
 

volley_boy1

New Member
Joined
Nov 23, 2009
Messages
9
I do unfortunately need all 31 sheets. Each sheet is a daily attendance log for each of our various programs (Swims, lessons, classes ect.)

Basically, staff go into each day and enter the attendance accordingly. The easy part is getting a total attendance number for the day or for an entire month (1 have one of those tabs at the end).

The hard part is that I have a second totals page where I can see per program what the attendence was. That way I can determine what programs are viable to continue running and which ones aren't (based on attendance numbers).

The formula I posted was kindly given to me a Barry Houdini who is an "Excel MVP" on this board and allows me to verify almost everything I need..... except for when I have different events happening at the same time frame (which i need to distinguish).

Sorry for the long posts. Just want to help clairify where I'm going with this!

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

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
Top