Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count IF Q
How could I express this in a formula- I wish to add up the number of
instances that stock doesn't exist and no sales have been made, hence a possible reason for no sales. e.g. Z8 = Sales in London which are 0 N46 = Stock for the London depot which is 0 Z12 = Sales in Manchester which are 20 N50 = Stock for the Manchester depot which is 0 Z16 = Sales in Bristol which are 0 N54 = Stock for the Bristol depot which is 0 The answer to above should be 2 I have a range of 30 locations, divided into 5 regions, but they are not in a contiguous range |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count IF Q
This may work for you.
=SUMPRODUCT(--(RIGHT(Z8:Z16,2)=" 0"),--(RIGHT(N46:N54,2)=" 0")) The two ranges used MUST be the same exact size. This also assumes that your data in both locations is in the same order. (ex. - it starts with London then Manchester followed by Bristol, and so-on for the rest of your regions. HTH, Paul -- "Sean" wrote in message ... How could I express this in a formula- I wish to add up the number of instances that stock doesn't exist and no sales have been made, hence a possible reason for no sales. e.g. Z8 = Sales in London which are 0 N46 = Stock for the London depot which is 0 Z12 = Sales in Manchester which are 20 N50 = Stock for the Manchester depot which is 0 Z16 = Sales in Bristol which are 0 N54 = Stock for the Bristol depot which is 0 The answer to above should be 2 I have a range of 30 locations, divided into 5 regions, but they are not in a contiguous range |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count IF Q
Thanks Paul, it won't work as my ranges are not Contiguous, in that
Z9; Z15 relates to a different region which I don't wish to COUNT within the first formula that I've shown as an example |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count IF Q
This is how I advanced, I placed the Region name in a column and used
the following formula =SUMPRODUCT((Z$8:Z$33=0)*(W$8:W$33="Midlands")*(N$ 46:W$71=0)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count IF Q
One small twist on this, how could I list the names of the locations
(which are in Z8:Z33) that have Zero sales and Zero Stock, in a string format. eg. "London, Manchester, Bristol" etc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |