Is there a Substitute to the AND Function

[volley_boy1]

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!

 

[Jonmo1]

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]

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]

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]

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]

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]

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!