Formula Help Please!!!

[CwBoy1963]

I hope I can explain this well enough, here goes......I have a spreadsheet divided out by different tabs and on one page it takes various data from these tabs and gives final numbers for a particular month. Here is one of my formulas I am having issues with: =IF('Inspections-Page 3'!$E$5:$E$69="Carson", COUNTIF('Inspections-Page 3'!$G$5:$G$69, "H"),0)

Here is what I am trying to get the formula to do, Find all of the cells labled Carson and if they have an 'H' in the corresponding cells then count these and put the number here. What ends up happening when I use the above formula is it ignores the IF statement and only does the COUNTIF of all of the 'H' which is not what I want. I think I am missing maybe a THEN statement or something else in the middle??? Can someone solve this for me? Thank you!!!

 

[ExcelChampion]

First, did you enter the formula exactly as it was presented to you or did you modify it?

If you modified it did you use whole column rnages (A:A, E:E, etc) in the formula? If so, this si a no-no.

Second, check that you do not have error values in the table of data that you are counting. If there is an error in the table then the formla will return an error.

 

[CwBoy1963]

Do you have to turn on the Shift+Ctrl+Enter for an array, it does not seem to matter if I hit just enter or the combo, it still comes back as an error. Anyone???

 

[ExcelChampion]

You do not need to turn on arrays. You simply press and hold your CTRL and Shift key and then hit Enter. To see if you did it correctly, look in the formula bar and the formula should be wrapped in brackets, as in:

{=Your Formula}

Also, you did not respond to my previous post.

 

[xenou]

Second, check that you do not have error values in the table of data that you are counting. If there is an error in the table then the formla will return an error

This is an important message from ExcelChampion's earlier post.

Regards.

 

[CwBoy1963]

Thank you, I figured out the Array thing, but because my formula was wrong it keeps erroring out. I have someone looking at it now, I think I bit off more than I can chew on this one. I put the formulas exactly as written and played some with them too, but to no avail. The problem is the cells that the data comes from are drop down boxes that give the inspector a choice of several items and they change constantly so the formula freaks out and errors out. I will send my spreadsheet to anyone who can figure it out. I know some people get a kick out of fixing these things because it's like a big puzzle. Thanks for the help already given!!

 

[cpis0002]

Hi, I just skimmed through the post and if I understood correctly the problem shouldn't be difficult to solve.
To make it simple, you could simply create an extra column with the formula:
=IF(AND('Inspections-Page 3'!$E$5:$E$69="Carson", 'Inspections-Page 3'!$G$5:$G$69="H"), "CH","").
and then =COUNTIF(new column,"CH")

If this doesnt work, or you want to fit it into one formula, just send the worksheet to me on chrispisani@gmail.com and i'll figure it out

 

[CwBoy1963]

Thanks Chris, sent a copy to you just now.

 

[xenou]

Just wanted to say I am still here to help if needed...let me know how things go.

Regards.

 

[CwBoy1963]

Thanks Alex, I appreciate the help........this forum is awesome!!!