[Solved] Using a formula to count multiple criteria

[richw68]

Hello,

I am struggling to try and find a way to do this with calc. I have the following fields:

Code: Select all

       A          B
1     v570        v7
2     v500        v5
3     v570        v7
4     v570        v6

I am trying to count the times that two values occur together. I know COUNTIF can be used to to determine the number of times a value occurs in a range of cells, but I have two values. In this case they are both text values as well. If I were to write it out in english it would be something like IF A1:A4="v570" AND B1:B4="v7" then count the row as 1. Then keep going to give me a total of how many times this occurs in the spreadsheet. I hope that is clear. Does anyone know what would be the best formula for this?

Thanks,
Rich

[JohnV]

Take a look at DCOUNT. The spreadsheet the example uses is at the top of the page.

[kingfisher]

You could also look at SUMPRODUCT. Each factor returns 0 (false) or 1 (true) and is then multiplied by the second factor, resulting in products of 0 or 1. Your formula would be something like =SUMPRODUCT(A1:A4="v570";B1:B4="v7") It's a long time since I looked at it so you may have to change my syntax.

[richw68]

Thanks to both of you! I was able to use DCOUNTA and it works great! Took me a bit to figure the function out, but putting the sample into my own sheet helped a lot in trying to figure out exactly what it was doing!

Thanks,
Rich