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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
Or even better, You said you are using XL2007

Use countifS

=COUNTIFS(Trade,"A",Category,"B")
 
Upvote 0
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( ?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
=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.
 
Upvote 0
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 "
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top