#### CwBoy1963

##### New Member
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!!!

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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

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

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,

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.

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.

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.

CwBoy1963 - a quick and dirty solution to this would be to set up a helper column which concatenates Cols E and G like this -
Code:
``=E1&G1``
Then do your countif on this new column, counting instances of "CarsonH".

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.

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.

Replies
3
Views
275
Replies
14
Views
641
Replies
2
Views
190
Replies
7
Views
429
Replies
1
Views
145

1,220,951
Messages
6,157,034
Members
451,393
Latest member
malcv

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back