Error is 'IF' formula

[JohnCR]

Hello,

I'm hoping someone can help with this formula...

=IF(H:H="1.13A",COUNTIF(INDIRECT("D1"&L9),"Verified"),0)

I count(if) the 1.13A's in the H column and store the number in L9. I would like to count the "Verified"'s in Col D starting at D1 and going to D(whatever is in L9)

Thanks in Advance,

John

 

[ETID]

How many "1.13A's are in col H ?

and is this a formula that you want to fill down?


...can you post an example?

 

[mscallisto]

you can always highlite portions of your formula and hit F9 to see the results of that portion. Hit escape when done.

Sometimes this helps

 

[JohnCR]

I just want to get the number of Verified's from column D.

For example...

Col Cell
D H L9=3
Verified 1.13A
Not Verified 1.13A
Not Verified 1.14A
Verified 1.13A

I count the "1.13A's" in column H and store that in cell L9(total of 3), I then want to know how many "Verifies" there are in column D when col H is equal to "1.13A starting at D1 and going down column D the number in Cell L9 (D1-D3).

Hope that helps,

Thanks,

John

 

[ETID]

Look in the help on the Dcount() function...
(There are a few more Dfunctions() as well)

If your data is in cells D:H

and assuming you have actual headers of "D" 7 "H" and not just using column ID's...I believe header names are going to be required.


D H
Verified 1.13A
Not Verified 1.13A
Not Verified 1.14A
Verified 1.13A


then in cells(n1819)...type this,

D H
Verified 1.13A


note: (this criteria definition can be typed any where), I just selected n1819
...this is where you can change the search criteria on the fly.(if you change "H" to 1.14A you get the counts of verified that have a value of 1.14A


So to count "D's" that are Verified and have a value an "H" of 1.13A, the formula in a cell of your choice "L9 in this case"

=Dcount(D:H,"D",n1819)

 

[ETID]

Ooops...
and assuming you have actual headers of "D" & "H" and not just using column ID's...I believe header names are going to be required.

 

[JohnCR]

It seems like that should work. I've 'named' D & H and pretty much followed you example but I'm only getting a '0'

 

[ETID]

what does your formula in L6 look like?

 

[JohnCR]

well, I tried naming D130 as 'D', and H1:H30 as 'H' and
then I entered =DCOUNT(D:H,"D",N1:O2). N1:O2 being ...

N O
1 Stat Inc
2 Verified 1.13A

Then I tried naming D1 to H30 as Data and column D as Stat and then tried =DCOUNT("Data","Stat",N1:O2) and =DCOUNT(D1:H30,"Stat",N1:O2)

I must be close just missing something little.

 

[JohnCR]

I also think it might have something to do with performing (or NOT in this case) equations. Most of the info I see has it listed as 'greater than/less than' scenario

 

[ETID]

Just to clarify....adding a header and naming a range are 2 differnet things. I am assuming that you did not name a range, is that correct?

 

[JohnCR]

hmm, no I used Insert|Naming. I'm not familiar with adding a header, I suppose.

 

[ETID]

Oh...use dcounta instead of dcount ...dcounta is the function to count non numeric items

 

[ETID]

If no joy using counta() then ...a header is simply a row added at the top of your data (usually row 1)..where you type in names for the columns

such as First Name, Last Name, Date Hired, etc.

 

[JohnCR]

Well, this is kinda interesting. If I address the criteria like I'm suppose to I get a big 0, if I enter criteria that is blank with nothing in it (Like I31:G33) I get a count of everything in column D.

hm, wierd. I'm trying to use a spreadsheet that had Access data imported into it. I wonder if that has anything to do with it.

 

[ETID]

It shouldn't...Have you tried =dcounta() instead of =dcount()?

 

[byundt]

Here's a completely different way of doing your count using the SUMPRODUCT function. Note that the SUMPRODUCT function used this way is very particular about the parentheses. You can shorten the 65535 part of the formula if you wish--it just won't work with D.
=SUMPRODUCT((D165535="Verified")*(ROW(D165535)<COUNTIF(H:H,"1.13A")))

This formula does not store the count of 1.13A in cell L9. If you need that capability, then use this formula:
=COUNTIF(H:H,"1.13A")

 

[JohnCR]

I was able to get DCOUNTA to work.

Apparently, having the criteria located above the data base is crucial. Once I moved the criteria above the data base everything worked fine.

Thanks for all the help.

 

[ETID]

....hmmmm that's a weird one, I have had no trouble with criteria location.