Formula Help

[pbajeff29]

I have a spreadsheet that I am needing to build formulas on. I have 3 qualifiers that need to be validated across a range. So the formula needs to first look in Column A on "Data" sheet and see is data in cell A1 exists AND look in Column E on "Data" sheet and see if data in cell A3 exists within the group that were identified through the first lookup AND look in Column B on "Data" sheet and see if data in B1 exists within the group that was identified through the first two lookups. Below is the formula I tried, but it is returning complete when not all 3 conditions are met. It has to go in order and look at the subset of results each step of the formula.

=IF(AND(COUNTIF(Data!$A:$A.$A$1)>0,COUNTIF(DATA!$E$E,$A$3)>0,COUNTIF(DATA!$B$B,$B$1)>0."Complete","Not Complete")

 

[jorismoerings]

Hi,

could you check the formula you provided With the one you are using. I suspect it’s just a type error but never the less. The formula provided is using the AND function without closing parenthesis after the latest COUNTIF.
This should be the correct way.

=IF(AND(COUNTIF(Data!$A:$A.$A$1)>0,COUNTIF(DATA!$E$E,$A$3)>0,COUNTIF(DATA!$B$B,$B$1)>0)."Complete","Not Complete")

depending on your excel version this can be done in a more efficient manner. if you update your account details and mentioning the office version used, you help us to help you.

please check if this solves your problem and if not, please post some sample data and show a manually calculated expected result.

hope this helps.

 

[pbajeff29]

Yes, the formula errors were from me manually typing it out. I cannot submit a sample/example as it is work related and confidential. But basically I am getting a Complete return if any combination of the items are true, not if all 3 are true for a single line. So for example, I got a complete, but the first count if was false and the second two were true. I need all three to be true on a single line to return the result. I updated the formula below and I am working in MS365. I can't use any special plug ins or anything since it is corporate systems.

=IF(AND(COUNTIF(Data!$A:$A.$A$1)>0,COUNTIF(DATA!$E$E,$A$3)>0,COUNTIF(DATA!$B$B,$B$1)>0),"Complete","Not Complete"))

 

[jorismoerings]

I need all three to be true on a single line to return the result.

The way your formula is phrased does exactly this however without a view of your data I can’t check if the countif function returns the correct result. But your first ask mentions this

But basically I am getting a Complete return if any combination of the items are true, not if all 3 are true for a single line.

So both statements contradict with each other. If you want any result, change the AND function to OR.

hope this helps.

 

[pbajeff29]

Again, I apologize for not being able to share. I can try to mock a fake file up to show what is happening. But the formula is not returning the results Complete if all three are true on a single line, they are returning the Complete if any are true.

 

[AhoyNC]

It would still help if you could post a fake file (maybe 5 rows) and just type in your expected results. Your current formula is not looking at each row, but is basically looking at whole columns. It's also best not to reference whole columns in your formulas.

 

[pbajeff29]

It would still help if you could post a fake file (maybe 5 rows) and just type in your expected results. Your current formula is not looking at each row, but is basically looking at whole columns. It's also best not to reference whole columns in your formulas.

I will work on a mock up when I get free time. But I need to look through the rows to find out if the entry was made. This is a SharePoint list with numerous options that pop on different columns. I need to search through a column and see if an individual name exists, then look at their entries from a particular date and then look through entries in the 3rd column to see if they did the activity.

 

[AhoyNC]

I think you are going to need to look at each row to see if it matches your criteria.
Maybe this example will be of some help.

Book1
	A	B	C	D	E	F	G
1	Name	Date	Active			Find
2	Name1	7/20/2021	Active1	Not Complete		Name	Name3
3	Name2	5/12/2021	Active2	Not Complete		Date	8/15/2021
4	Name3	8/15/2021	Active5	Complete		Active	Active5
5	Name4	8/15/2021	Active4	Not Complete
6	Name5	8/15/2021	Active5	Not Complete
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IF(AND($G$2=A2,$G$3=B2,$G$4=C2),"Complete","Not Complete")

 

[Marcelo Branco]

I think you are going to need to look at each row to see if it matches your criteria.

Hi Ahoy

Using your data sample
G5
=IF(COUNTIFS(A:A,G2,B:B,G3,C:C,G4),"Complete","Not Complete")

M.