IF( and COUNTIF( Problems

[TJHARJ]

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

 

[Jonmo1]

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]

Or even better, You said you are using XL2007

Use countifS

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

 

[TJHARJ]

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]

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]

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]

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]

=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]

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]

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