I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.
Situation:
I have column B that contains initials and I have column C that contains a type (of store)
so it'd look like this
jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET
and I want to have a count of how many RET's there are for Initials JP ect
Re: Dcount
Nichola08,
I think Dave's advice would be to use a Pivot Table if the list is long and you want to cover all the bases quickly. My number two choice would be a formula like below.
=SUMPRODUCT((A1:A9="jp")*(B1:B9="RET"))
Third on my list would be DCOUNTA (for text) as you asked and you can hover over the linked word in your post to get a good example of that.
Jim
Re: Dcount
I don't want to use a pivot table since I already have it in excel. As for the sumif to me it looks like that would make it multiply the first criteria count (if JP) by the second (if RTO) which would not generate the right number I want to know how many RET are JP how many RET are BM ect. . . This is why I was trying to use the Dcount situation but I'm counting from different worksheets and it doesn't ever return a number. Here is what I'm using maybe my syntax is wrong:
=DCOUNT('PT Aug 07'!D1:F569,'PT Aug 07'!D1,"JP")
I know I could do it with dcount then use an "if" but I'm not very good at nesting the functions together either. . .
Re: Dcount
Nichola08,
Did you try the formula I gave you? Do you know how to do a Pivot Table in Excel? Don't be too hasty. Both will work. You'll have to edit the formula for your purposes, but it will work. Experiment and post back.
Jim
Re: Dcount
Nichola, A lot of people are misled by the name of SUMPRODUCT. In fact when used as Jim has shown, it counts multiple conditions. I have used these words to provide links that should explain SUMPRODUCT. See also [mcs]*[[/mcs]
Re: Dcount
Ok I got you. I'm sorry it's just this has really frazzled me but yes it did work in fact i gave it a third criteria which helped me out on another step as well. Thank you sorry for my ignorance. .
Re: Dcount
You're very welcome, glad to help.
Don’t have an account yet? Register yourself now and be a part of our community!
