Results 1 to 15 of 15

20020125, 13:42 #1
 Join Date
 Nov 2001
 Location
 Watford, Hertfordshire, England
 Posts
 129
 Thanks
 0
 Thanked 0 Times in 0 Posts
countif with two criteria? (2000 sr1)
I need to count the entries in column b that are over 0 (not negatives or 0) and where the data in the same row in column a is equal to "E".
For example;
A B
a 4
b 7
e 6
e 9
a 7
Would give me the answer 6. I have tried a host of combinations with Countif, If, and the like array entered and I cannot get around this problem. Help!Neil Eustice
Woody Worshipper

20020125, 14:06 #2
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: countif with two criteria? (2000 sr1)
Neil, if you really meant count in the Excel sense then the answer is 1 and the formula is {=COUNT(IF((B1:B5>0)*(A1:A5="e"),1,""))}. However, if you meant sum, then the answer is 6 and the formula is {=SUM(IF((B1:B5>0)*(A1:A5="e"),B1:B5,""))}
In both cases, the {} are entered by Excel, not you, because these are array formulas and are entered with <Ctrl><Shift><Enter>. HTH Sam<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20020125, 14:49 #3
 Join Date
 Nov 2001
 Location
 Watford, Hertfordshire, England
 Posts
 129
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: countif with two criteria? (2000 sr1)
Appologies, I did mean count, and typing it in a hurry put 6 instead of 1. Thanks anyway.
Neil Eustice
Woody Worshipper

20020125, 15:25 #4
 Join Date
 Nov 2001
 Location
 Watford, Hertfordshire, England
 Posts
 129
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: countif with two criteria? (2000 sr1)
Another problem for you. What if I wanted to sumproduct on columns b and c but only where column A was = "E"?
Neil Eustice
Woody Worshipper

20020125, 15:40 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,298
 Thanks
 3
 Thanked 203 Times in 187 Posts
Re: countif with two criteria? (2000 sr1)
Hi Neil,
Try something like:
=sum((A1:A5="E")*(B1:B5)*(C1:C5))
arrayentered (adjust ranges as necessary).
Hope that helps.Regards,
Rory
Microsoft MVP  Excel

20020125, 16:04 #6
 Join Date
 Nov 2001
 Location
 Watford, Hertfordshire, England
 Posts
 129
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: countif with two criteria? (2000 sr1)
This includes cells where the data in C is a negative. Just to make things harder, I only want to sumproduct where data in column A is = "E" and data in column c is >0.
Thanks for the help.Neil Eustice
Woody Worshipper

20020125, 16:17 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: countif with two criteria? (2000 sr1)
Neil, in your original example there was no column C so I'm a tad confused, but does
{=SUM((column_A_range>0)*(column_B_range="e")*(col umn_A_range))}
array entered, do what you want or get you in the right direction?John ... I float in liquid gardens
UTC 7ąDS

20020125, 16:27 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,298
 Thanks
 3
 Thanked 203 Times in 187 Posts
Re: countif with two criteria? (2000 sr1)
Oh OK. Then either:
=sum((A1:A5="E")*(B1:B5)*(C1:C5>0)*(C1:C5))
or
=sum((A1:A5="E")*(B1:B5)*if(C1:C5>0,C1:C5,0))
both arrayentered should do it.
Hope that helps.Regards,
Rory
Microsoft MVP  Excel

20020125, 16:37 #9
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: countif with two criteria? (2000 sr1)
<img src=/w3timages/blackline.gif width=33% height=2>
> sumproduct on columns b and c but only where column A was = "E"?
<img src=/w3timages/blackline.gif width=33% height=2>
Assuming you added a column of numbers in C1:C5:<pre>{=SUMPRODUCT(IF(A1:A5="e",B1:B5,0),IF(A 1:A5="e",C1:C5,0))}</pre>
entered w/o the braces as an array formula.<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20020128, 12:35 #10
 Join Date
 Nov 2001
 Location
 Watford, Hertfordshire, England
 Posts
 129
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: countif with two criteria? (2000 sr1)
Thanks
Neil Eustice
Woody Worshipper

20020128, 12:39 #11
 Join Date
 Nov 2001
 Location
 Watford, Hertfordshire, England
 Posts
 129
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: countif with two criteria? (2000 sr1)
{=SUMPRODUCT(IF(A1:A7="e",B1:B7,0),IF(B1:B7>0,C1:C 7,0))} array entered is the idea I was after. This anwser came from SammyB and I just adjusted it to suit my needs. Sorry about any confusion but I was amending what I wanted to do on the fly.
CheersNeil Eustice
Woody Worshipper

20020128, 19:43 #12
 Join Date
 Jan 2001
 Location
 Nova Scotia
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: countif with two criteria? (2000 sr1)
I won't crticize the solutions presented which are probably correct. However, I personally find them rather complicated in spite of the fact that I have been using Excel for too many years to remember. I often wonder why people don't create an extra column in this case with the appropriate logic to calculate the impact on one cell. Then you can copy that logic to all the cells in the column and perhaps sum the total at the bottom. Perhaps it will create a larger file but the logic is eminently easier to follow and probably correct. In addition, the process is usually easier to scale up to more difficult problems.

20020128, 19:56 #13
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: countif with two criteria? (2000 sr1)
Amen, I wrote them & I hate them. I would aviod an array formula at all costs. Sam
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20020128, 21:09 #14
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: countif with two criteria? (2000 sr1)
To each his own. We are all guilty of excessive spreadsheet elegance at some time.
On one hand I prefer the simplest solution (Occam's razor) but I have a strong preference for single cell formulas for layout reasons. I get too much multicolumn true mean calculations from people who don't use =SUMPRODUCT as it is; using extra cells when it is isn't necessary can obscure the intent and focus of the analysis.
But I also think you should consider one other reason to use array formulas in the context used here; they are much easier than setting up an equivalent =DSUM(,,) formula, which is the only other way to do complex criteria formulas.John ... I float in liquid gardens
UTC 7ąDS

20020129, 08:17 #15
 Join Date
 Nov 2001
 Location
 Watford, Hertfordshire, England
 Posts
 129
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: countif with two criteria? (2000 sr1)
I partially agree with you but it is not always possible to put in that extra column. It depends what is going on with the rest of the data and other automated tasks already set in motion. However, a good point and certainly one worth thinking about when planning new spreadsheets.
Neil Eustice
Woody Worshipper