Problem with function for count with criteria

**masquerader** · 03-23-2008, 04:16 PM

Hi,

I would really appreciate if someone helped me with getting a function/formula for counting number of occurrences in a worksheet.

My requirement is to count the number of times an event occurs in a column (say) B, provided another event occurs in column (say) A. However if another event occurs in column (say) C, then count for B for that particular row need not be counted.

I discovered this problem when I had to make counts for individual coumns B & C with the same condition met in A using array function of SUM for two criteria. However certain events occurred both in B & C for which i want only one count (as once in B or C) and not two counts (as once in B & once in C.). Basically I want the count to be shown only in B without it being counted in C.

I hope this is not too confusing...and somebody might be able to help.

Thank you.

**darkyam** · 03-23-2008, 04:22 PM

Quick question: in column B, are you counting actual instances where there is a number or are you wanting to sum the numbers for each row in which your criteria are met?
If it is simply a count of instances, =SUMPRODUCT((A1:A8="Yes")*(C1:C8="No")) where "Yes" means the condition in A has been meant and "No" means that the condition has not been meant (meaning that the instance in B should be counted because the event in A has occurred and the one in C has not). If you are wanting to sum B where this happens, =SUMPRODUCT((A1:A8="Yes")*(C1:C8="No"),B1:B8)

**masquerader** · 03-23-2008, 04:36 PM

Thanks for the speedy reply. I want to count the instances in B where criterion of A is met, not the sum of the numbers in the rows.

**daddylonglegs** · 03-23-2008, 04:36 PM

It would probably help if you provide the formula you are currently using, and an indication of the circumstances in which that formula gives results you don't want.

**masquerader** · 03-23-2008, 04:42 PM

{=SUM(('Master log'!K:K<>"")*('Master logbook'!D:D="HJT"))}

**daddylonglegs** · 03-23-2008, 04:46 PM

...and that doesn't work because......?

P.S are you using Excel 2007?

**masquerader** · 03-23-2008, 04:52 PM

The function is working. I am actually counting two events

{=SUM(('Master logK:K<>"")*('Master logD:D="HJT"))}
and
{=SUM(('Master logL:L<>"")*('Master logD:D="HJT"))}

Both counts are mutually exclusive. however a few events are there in both K & L, I don't want the count of the first to be included in The second as well.

And yes I am using excel 2007

**daddylonglegs** · 03-23-2008, 05:02 PM

Perhaps just make the second formula

{=SUM(('Master log'!K:K="")*('Master log'!L:L<>"")*('Master log'!D:D="HJT"))}

or if you want to count both in one formula

{=SUM(('Master log'!K:K&L:L<>"")*('Master log'!D:D="HJT"))}

**masquerader** · 03-23-2008, 05:11 PM

Thanks a ton daddylonglegs & darkyam.

Here's the glitch.

I want the count to be under K if K>L and under L if K<L

How do I do this?

Sorry and thanks again.

**darkyam** · 03-23-2008, 05:19 PM

=SUMPRODUCT(('Master log'!K:K>'Master log'!L:L)*('Master log'!D:D="HJT"))
Then just switch the sign from > to < for the second formula. One should have an = after the sign to take care of that contingency.
For the second formula, if the first is in A1 on it's sheet, you could also do =Countif('Master log'!D:D,"HJT")-A1 instead of a second SUMPRODUCT formula. It might run a little faster than that formula if there's a ton of data.

**masquerader** · 03-23-2008, 05:30 PM

Dear daddylonglegs and darkyam,

I am afraid the formulae you gave are only giving me the count of how many times the event occurs in both K & L. whereas I wanted a separate total of K and a separate total of L. Where K>L (in cases where it occurs in both) it would add to total of K and where L>K (in cases where it occurs in both) the count would add to that of L.

**darkyam** · 03-23-2008, 05:42 PM

I want to count the instances in B where criterion of A is met, not the sum of the numbers in the rows.

Our responses have been based on the above. If you want a sum, =SUMPRODUCT(('Master log'!K:K>'Master log'!L:L)*('Master log'!D:D="HJT"),'Master log'!K:K) and =SUMPRODUCT(('Master log'!K:K<'Master log'!L:L)*('Master log'!D:D="HJT"),'Master log'!L:L). Again, either the first will have to be >= or the second <= to account for instances in which they're equal.