Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
Quote:
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.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#4
|
|||
|
|||
Ahhh many thanks Claus. That worked a treat!
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
|
|||
|
|||
Brilliant Claus!
Thanks again for your help with these two issues! You've been a great help! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |