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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,742
Messages
5,655,030
Members
418,171
Latest member
ramiroayala

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top