Help with CountIF / SUM
 

Hello,

I'm not sure if this is in the right forum, apologies if not. I couldn't see a general formular help section.

I have some data and need to do a COUNTIF but based on two seperate conditions. I need it to count items only if there is a "3" in column J AND a "7200" in column B.

I've tried using the following:

COUNTIF(QBR!B:B,7200)+COUNTIF(QBR!J:J,3)

However this adds the total of "7200"s and the total of "3"s, regardless if they 'match' in the same row.

I also tried:

COUNTIF(QBR!B:B,7200)AND*(QBR!J:J,3)

with no luck and:

SUMPRODUCT((QBR!B:B="7200")*(QBR!J:J="3"))

But this just returns 0, not sure why.

Does anyone have any ideas on how to get this working?

Many thanks for any help you can offer.

Help with CountIF / SUM
 

Hi Pete,

Am Wed, 9 May 2012 09:15:15 +0000 schrieb pete212:

I have some data and need to do a COUNTIF but based on two seperate
conditions. I need it to count items only if there is a "3" in column J
AND a "7200" in column B.

you are looking for numbers. Therefore you have to write the numbers
without quotes:
=SUMPRODUCT(--(QBR!B1:B100=7200),--(QBR!J1:J100=3))
or
=SUMPRODUCT((QBR!B1:B100=7200)*(QBR!J1:J100=3))
or if you have xl2007 or later:
=COUNTIFS(QBR!B:B,7200,QBR!J:J,3)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Dear pete212, Good Morning.

It´s a little dificult to me to understand what you really want to sum.

Any chance you could show us an example of the workbook to make it easier to understand the issue? Attach it here.

Ahhh many thanks Claus. That worked a treat!

Help changing the sheet name
 

Hi Pete,

Am Mon, 14 May 2012 13:53:15 +0000 schrieb pete212:

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Pete"

the new sheet is the active sheet. Try:
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Pete"

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Help changing the sheet name
 

Hi Pete,

Am Mon, 14 May 2012 13:53:15 +0000 schrieb pete212:

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Pete"

only one sheet can be named "Pete". Before you add a new sheet which
should be named "Pete", you have to rename the last sheet (Pete) with
another name:
Sheets(Sheets.Count).Name = "Sheet" & Sheets.Count
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Pete"


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Brilliant Claus!

Thanks again for your help with these two issues! You've been a great help!

:)