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!!!

 

[xenou]

One way is with an array formula:

=SUM(('Inspections-Page 3'!$E$5:$E$69="Carson")*('Inspections-Page 3'!$G$5:$G$69="H"))

Confirmed with Control + Shift + Enter

I'll bet there are other ways to do this too. Regards

 

[CwBoy1963]

Thank you, but that did not work. I thought * was mutiply? How do you multiply words with out telling the formula that you are counting the words within the box and then making them into a number? I am confused!!

 

[xenou]

Hmmm...

If the Cell in Column 1 is Carson (TRUE) then the true will evaluate to 1, and if the Cell in Column 2 is H (TRUE) then the true will evaluate to 1, so:

1 * 1 = 1,
and all the 1's added up should be your count.

You entered the formula with Ctrl + Shift + Enter, correct? Not just enter.

Let me think here...

Edit: note, FALSE is 0, so anything times 0 is 0 ... hence, not counted.

 

[xenou]

Try:

=SUMPRODUCT(('Inspections-Page 3'!$E$5:$E$69="Carson")*('Inspections-Page 3'!$E$5:$E$69="H"))

This is not an array formula, so just Enter when you put the formula in.

 

[xenou]

Explanation in detail of this formula:
http://www.ozgrid.com/Excel/Excel-conditional-summing.htm

 

[CwBoy1963]

Alex,

That did not work either..hmmmm. Can I send the spreadsheet to you possibly and you look at it? I did not do the spreadsheet myself, I am just working on it for a coworker. It was done in Excel 2002 and I moved it to my computer with Excel 2003 on Windows 2000. We work in the State of Nevada Fire Marshal Office and this spreadsheet tabulates statistics of HAZMAT and Fire & Life Safety Inspections done throughout the State. I have appointments all day today and tomorrow so if I dont get in touch with you until Monday, you know why. I appreciate any help you can render. Thank you for your efforts thus far.

 

[Gerald Higgins]

CwBoy1963 - a quick and dirty solution to this would be to set up a helper column which concatenates Cols E and G like this -

=E1&G1

Then do your countif on this new column, counting instances of "CarsonH".

 

[gardnertoo]

Alex's array formula does work. I built a test sheet to try it out, resluts in the photo:

I copied the text of his array formula, pasted it into a cell. Then instead of hitting enter or just clicking away, you must hit CTRL+SHIFT+ENTER. This gives you an array formula instead of a normal formula.

From your description of the application, I made a guess that your list will be various inspections for various counties in Nevada. I generated a list of counties at random in the range $E$5:$E$69, and a random list of made-up inspection types in $G$5:$G$69. I made sure Carson had at least one "H" type inspection. Then I applied the array formula to my invented list, and it worked. I then altered the array to look up the county and inspection type, again using the CTRL+SHIFT+ENTER key combination. I then copied across and down by dragging the corner of the cell like you would for any normal formula.

If you send me your email addres, I can send you the sheet.

 

[CwBoy1963]

Thank you, I will try it all out on Monday. 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.