IF( and COUNTIF( Problems

TJHARJ

New Member
Joined
Aug 6, 2010
Messages
9
I am working to create a tool that uses a dump sheet, and sheet that will be populated once the dump sheet is filled.

I have 4 main catagories that i have used COUNTIF( to sort. Within each catagory there are 4 sub catagories.

I have tried to do =if(Trade,"A")=countif(Catagory,"B"),"0")

and I am getting to results.

What I need is the count of"B" if "A" is met.

After that I will need to break those two catagories down again similar to what I did with A to B.

I am using microsoft excel 07.

Please respond if you have an idea of what I am doing wrong or another method for obtaining this information.

Thanks
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

Sumproduct is best for this..

=SUMPRODUCT(--(Trade="A"),--(Category="B"))

note, that ranges in sumproduct cannot be entire column refs like A:A.
You must use specific row #s like A1:A1000
Unless in XL2007, and even then it's not recommended to use entire column refs.

Hope that helps.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Or even better, You said you are using XL2007

Use countifS

=COUNTIFS(Trade,"A",Category,"B")
 

TJHARJ

New Member
Joined
Aug 6, 2010
Messages
9
Thank you, that worked great.

The next step in my process is to add a sum of the data if these criteria are met.

Would I simiply change it to Sumifs( instead of countifs( ?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Sumifs is the same as countifs, just add the range to sum at the beginning

=SUMIFS(RangeToSum,Trade,"A",Category,"B")

for sumproduct, it's this

=SUMPRODUCT(--(Trade="A"),--(Category="B"),RangeToSum)
 

TJHARJ

New Member
Joined
Aug 6, 2010
Messages
9
What exactly do you mean by sum range?
(sorry relativly new to these formulas in excel)

Also, I hit a bit of a snag on the COUNTIFS(
The first Trade worked well, but when I moved to the second of the 4 total trades, it gave me a result of 0.

I sorted my dump sheet, and I know there are 12 cells that meet my 2 criteria.

The forumla I used was =COUNTIFS(Trade, "A", Category, "B")

A being the first criteria and B being the second.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

What exactly do you mean by sum range?
You said the next step was to add a sum of these criteria...
What range would you like to sum based on that criteria? That is the RangeToSum

I want to sum ColumnC IF ColumnA = "Jon" and Column B = "Cool"
Column C is the RangeToSum
=SUMIFS(C1:C100,A1:A100,"Jon",B1:B100,"Cool")

Also, I hit a bit of a snag on the COUNTIFS(
The first Trade worked well, but when I moved to the second of the 4 total trades, it gave me a result of 0.
Post the exact formula you used, dont just put in Trade and Category...put in the actual Range References..
And it would help to include a sample set of data.
You can just copy and paste it from excel into the response window of the thread.
 

TJHARJ

New Member
Joined
Aug 6, 2010
Messages
9
=COUNTIFS(Trade, "A", Category, "CAPITAL")

Is almost the exact fomula I am using. I used Trade and Category because I renamed the full columns to siplify how many times I need to switch sheets.

The colums are E:E and H:H and they are on a sheet called "Dump Sheet"

As for the clarification on the Sumifs( worked for all the data except trade B which is where I'm having the COUNTIFS( problem.

I can't post the actual formula or any data, it contains private information.

Would there be an issue with the format of the cell? general vs text.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
All I can say is to check the spelling of "B" in Trade and within the formula.
They should match exactly.

Look for extra spaces...
"B"
" B"
"B "
 

TJHARJ

New Member
Joined
Aug 6, 2010
Messages
9
Thanks JonMo that got it working.

I went through and added that in to the tool for the other criteria as a back up when this is used down the road.

Thanks again.

-Travis
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,505
Messages
5,511,705
Members
408,862
Latest member
sidneybc

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top