IF( and COUNTIF( Problems

TJHARJ

New Member
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Jonmo1

MrExcel MVP
Welcome to the board...

Sumproduct is best for this..

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
Or even better, You said you are using XL2007

Use countifS

TJHARJ

New Member
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
Sumifs is the same as countifs, just add the range to sum at the beginning

for sumproduct, it's this

TJHARJ

New Member
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
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

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

Replies
3
Views
145
Replies
7
Views
195
Replies
5
Views
78
Replies
1
Views
56
Replies
3
Views
108

1,171,628
Messages
5,876,524
Members
433,199
Latest member
guerin47

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.

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

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