# Count if based on info in 2 collumns

#### warrima

##### New Member
I need a formula that will count the values in column B if the values in collumn A say a certain word. For example i have three order types - urgent, plan and stock (collumn A) and i need to count the number of orders of each type. Collumn B has the order numbers. Additionally the order numbers may be duplicated so i only need to count the individual numbers. I found a sum if freq formula but this counts without looking to see whats in collumn A. Can anyone help with this?

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
If you're using Excel 2007, use this:

Code:
``````=SUMIFS(B1:B5,A1:A5,"Urgent")
=SUMIFS(B1:B5,A1:A5,"Plan")
=SUMIFS(B1:B5,A1:A5,"Stock")``````

the above works if your Order types are in cells A1:A5 and your Values are in B1:B5.
It sums the values in column B if Column A contains the Value "Urgent".

Each of the three formulas will SUM the values for each type of Order respectively

Last edited:
Sorry heres an Example to make it more clear!

A B C
1 Plan 1222 Part 1
2 Plan 1222 Part 2
3 Stock 1345 Part 1
4 Emergency 2354 Part 1
5 Plan 3334 Part 1
6 Stock 5566 Part 1

So in this example i have 5 orders in total ( order 1222 has 2 parts so only counts once). 2 plan orders, 2 stock orders and 1 emergency order. Really need help with a formula that can calculate this!

Thanks

If you're using Excel 2003, Use this:

Code:
``````=SUMPRODUCT(--(A1:A5="Urgent"),--(B1:B5))
=SUMPRODUCT(--(A1:A5="Plan"),--(B1:B5))
=SUMPRODUCT(--(A1:A5="Stock"),--(B1:B5))``````

Thanks I have Excel 2003, will it work with it also

Ok, I don't understand what you're SUMing.

You need it to SUM what?

Which is the criteria and which is the value?

Are you counting how many orders, or counting how many parts for each order?

Sorry heres an Example to make it more clear!

A B C
1 Plan 1222 Part 1
2 Plan 1222 Part 2
3 Stock 1345 Part 1
4 Emergency 2354 Part 1
5 Plan 3334 Part 1
6 Stock 5566 Part 1

So in this example i have 5 orders in total ( order 1222 has 2 parts so only counts once). 2 plan orders, 2 stock orders and 1 emergency order. Really need help with a formula that can calculate this!

Thanks

What is the expected outcome?

I'm counting the total orders, however some orders have several parts so the o/n are duplicated. That is why i had the sum if frequency formula as it counted only the total number of different o/n's.

I'm counting the total orders, however some orders have several parts so the o/n are duplicated. That is why i had the sum if frequency formula as it counted only the total number of different o/n's.

What is the outcome/result you are looking for?

Its part of a large spreadsheet which tracks order status. It is used for the KPI of our sales staff. They have targets to recieve and dispatch orders in a certain time. I need the formulas to pull all this info out of the tables each month.

If this formula works i will use it to match delivery performance against order type. Emergency orders need dispatched inside 2 working days. Plan inside 1 week etc

Replies
3
Views
120
Replies
8
Views
163
Replies
7
Views
156
Replies
9
Views
318
Replies
4
Views
531

1,203,690
Messages
6,056,753
Members
444,889
Latest member
ibbara

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

### Which adblocker are you using?

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