Formula Help Please!!!

CwBoy1963

New Member
Joined
Jul 25, 2007
Messages
7
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!!! :confused:
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Jul 25, 2007
Messages
7
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 

CwBoy1963

New Member
Joined
Jul 25, 2007
Messages
7
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
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".
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
Alex's array formula does work. I built a test sheet to try it out, resluts in the photo:

907301696_25fbd17103_m.jpg


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

New Member
Joined
Jul 25, 2007
Messages
7
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. :confused:
 

Forum statistics

Threads
1,181,412
Messages
5,929,784
Members
436,692
Latest member
ModestMuse

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top