#VALUE! Error Help

[Timberwolf]

Hi all

I'm trying to write a countifs formula that will check one cell for a date range and then check another cell for the word "yes". The formula works with criteria one and two for checking the date range, but when I add the last criteria I get a #Value ! error. Cell I2 is formatted as text. see the code below (Remember I'm self taught and a horrible teacher lol)

=COUNTIFS('Table Maintenance Reports'!E1:E9344,">=01/01/2019",'Table Maintenance Reports'!E1:E9344,"<=31/01/2019",'Table Maintenance Reports'!I2,"Yes")

Thank you for any assistance you can offer.

 

[Trevor_S]

The third criteria refers to a single cell, not a range.
If you only want the countifs to be performed on the other two ranges if I2 contains Yes, then put the countifs inside an if formula:
=IF('Table Maintenance Reports'!I2="Yes",COUNTIFS('Table Maintenance Reports'!E1:E9344,">=01/01/2019",'Table Maintenance Reports'!E1:E9344,"<=31/01/2019"),0)

 

[Timberwolf]

Sorry I copied and pasted that formula from somewhere else in my spread sheet.

Basically what I have is a table with the date in column E if it finds a date within the specified range I need it to look for "yes" in columns I,L,O,R,U,X,AA in the same row, each time it finds "yes" in those cells I want it to add 1 to the value where this formula is being added. I need this to check each row of sheet "Table Maintenance Reports" as each row is a separate report.

I have a table with "Table Maintenance Reports" on one sheet and a "Parts Replaced Per Month 2019" table on another sheet. The "yes" or "no" in cells refers to whether or not they part was a warranty claim and I only want the ones that are yes.

I hope this clears it up a bit or I just really confused you lol. Thanks again for your help

 

[Timberwolf]

.

 

[Trevor_S]

Ok, so column E contains dates, and the various other columns you’ve specified may contain Yes.

Are you looking to:

• Put a formula on each row, that counts the number of times Yes appears on that row if the date is within a given range; or

• Create a single formula somewhere outside of the table that looks at the entire table?

If its the latter, do you want it to return:

• The number of rows where the date is within the given range and there is at least one Yes in the row; or

• The total number of times Yes appears in rows where the date is within the given range (assuming that some rows may contain more than one Yes)?

Also does Yes appear in any of the columns that you haven't specified? If it doesn't, the formula could check a whole range in one go, rather than looking at individual columns.

 

[Timberwolf]

Thank you for your help Trevor_S

Yes I want to

• Create a single formula somewhere outside of the table that looks at the entire table?
• The total number of times Yes appears in rows where the date is within the given range

If the range goes from I2:AA2 that would work instead of just the specified cells.

The table this formula is going in to is on Sheet "Parts Replaced Per Month 2019" cell C3

The table it will be checking is on Sheet "Table Maintenance Reports" the formula needs to look at each row,one at a time, check the date in the E2 then I2:AA2 for the word YES. then check date E3 then look for "yes" in I3:AA3 and so on and sum I would like the sum on C3 of Sheet "Parts Replaced Per Month 2019"

Thank you

 

[Trevor_S]

There probably is a way to look at all columns in one go, but my idea didn't work. The following should, but its a long formula!

=COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!I:I,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!L:L,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!O:O,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!R:R,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!U:U,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!X:X,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!AA:AA,”Yes")

 

[Timberwolf]

When I input that code there is a glitch in it as excel sees all the below text as Criteria3

”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!L:L,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!O:O,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!R:R,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!U:U,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!X:X,”Yes")+COUNTIFS('Table Maintenance Reports'!E:E,">=01/01/2019",'Table Maintenance Reports'!E:E,"<=31/01/2019",'Table Maintenance Reports'!AA:AA,”Yes")

 

[Trevor_S]

Sorry, I think I can see the issue. Because it was such a long formula, I assembled it in Word rather than in the reply window. Word has replaced the " before each word Yes with ” which is a slightly different character and so isn't recognised by Excel. If you change the quotes before each Yes back to " it should work.

 

[Timberwolf]

Hello Trevor_s

Sorry for the delay in my response, things got crazy and I haven't gotten a chance to work on this.

It worked great! thank you so much.