Count if based on info in 2 collumns

[warrima]

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?

 

[Mr. Walnuts]

If you're using Excel 2007, use this:

=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

 

[warrima]

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

 

[Mr. Walnuts]

If you're using Excel 2003, Use this:

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

 

[warrima]

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

 

[Mr. Walnuts]

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?

 

[Aladin Akyurek]

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?

 

[warrima]

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.

 

[Aladin Akyurek]

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?

 

[warrima]

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