COUNTIFS formula as a range

**yadaaa** · 07-04-2008, 09:24 AM

Hello,

I'm trying to get a list of items that has two versions, and want to make a table that will count he total number of items, and them within that list, count the total of version 1 and the total of version 2.

I tried doing:
=COUNTIFS(COUNTIFS(sheet1!B:B, B2), ver1)

as you can see, I try using another countifs as the range criterion. I'm doing it like this, because I have 200 places with these items, and cell B2 defines the place name, so the inner countifs gives me the total items in a certain place, and the outer countifs should give me the specific version from the total items of this certain place.

why isn't it working? can't I have another countifs as a range critertion?

Thanks

**NBVC** · 07-04-2008, 09:41 AM

The Countifs needs a range specified not a number... so that won't work.

Are you trying to count based on 2 criteria (i.e that column B contains item in B2 and that another column contain "ver1")?

**yadaaa** · 07-04-2008, 12:19 PM

That Ver1 is in another column, in the range found before (IE. Column B contains Item B), like 'Find' and then 'Find in this search'

**NBVC** · 07-04-2008, 12:36 PM

still don't quite understand what you are trying to do... perhaps you can provide an example showing what you have and what you want to achieve?

**yadaaa** · 07-04-2008, 12:47 PM

Ok,

I have a column like 'clinic1' with clinics 1-200.

I have the column F with 'main' and 'reg'.

I need to count the number of occurrences of clinic1 in the table (which is 37 (out of about 7000)) and the the number of times 'main' appears (which is 5)

I found something, but I can't get it to work: this is from excel's help, "Count how often multiple number values occur by using functions"

=COUNT(IF((A2:A11="South")*(C2:C11="Meat"),D2:D11))

But I can't get it to work; I don't understand what the last range is for.

Thanks

sorry: this is what I tried

=COUNT(IF((A1:A37="Clinic1")*(F1:F37="Reg"),G1:G37))

**NBVC** · 07-04-2008, 12:55 PM

Try either:

=Countifs(A:A,"Clinic1",F:F,"Main") - If you have XL2007

or

=Sumproduct((A1:A7000="Clinic1")*(F1:F7000="Main")) - if you have other versions... note you can't use whole column references here like A:A....

Not sure why you need column G.. it is empty... are you trying to sum based on 2 criteria or count?