Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count & If fomula
Hello
There is a rutine counting each week (very boring) I want to make it authomatic. I have an spread sheet which 4 columns are important: city, city status (On/Semi), state, %complete I want this counting perocess: 1- I want to count: How many cities in each state are in "On" status? 2-I want to count: How many cities in each state are "100%" complete? Please Help me to get ride of this boring manual updating. Thanks BHR |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count & If fomula
Try...
=SUMPRODUCT(--(StateRange=State),--(StatusRange=Status)) and =SUMPRODUCT(--(StateRange=State),--(PercentageCompleteRange=PercentageCom plete)) Hope this helps! In article , Bahareh wrote: Hello There is a rutine counting each week (very boring) I want to make it authomatic. I have an spread sheet which 4 columns are important: city, city status (On/Semi), state, %complete I want this counting perocess: 1- I want to count: How many cities in each state are in "On" status? 2-I want to count: How many cities in each state are "100%" complete? Please Help me to get ride of this boring manual updating. Thanks BHR |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count & If fomula
Thanks Domenic.
The first formula works well. but the second one doesn't. I put =SUMPRODUCT(--('Completed Cities'!G4:G400="IL"),--('Completed Cities'!P4:P400"100%")) but it doesn't work. It says #N/A. but values exist until raw 229. I put the formula to raw 400 as I will add raws daily at bottom and I don't want to change the formula each day. but If this is the case why the first formula works well? I put the first one as : =SUMPRODUCT(--('Completed Cities'!G4:G400="IL"),--('Completed Cities'!C4:C400="on")) and another question, Is it possible that I put two condition as: How many cities in state are "On" or "pending" status? Really Thanks BHR "Domenic" wrote: Try... =SUMPRODUCT(--(StateRange=State),--(StatusRange=Status)) and =SUMPRODUCT(--(StateRange=State),--(PercentageCompleteRange=PercentageCom plete)) Hope this helps! In article , Bahareh wrote: Hello There is a rutine counting each week (very boring) I want to make it authomatic. I have an spread sheet which 4 columns are important: city, city status (On/Semi), state, %complete I want this counting perocess: 1- I want to count: How many cities in each state are in "On" status? 2-I want to count: How many cities in each state are "100%" complete? Please Help me to get ride of this boring manual updating. Thanks BHR |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count & If fomula
Thanks Domenic. The other issue also solved (It was becuase some cells had
#N/A content). but still I don`t know how to put 100% for second question (as I saw that in some cities we had more than 100% progress in this phase.) and If it is possible for 1st question to add another condition as: How many cities in each state are "on" or "pending" status? Really Thanks BHR "Domenic" wrote: Try... =SUMPRODUCT(--(StateRange=State),--(StatusRange=Status)) and =SUMPRODUCT(--(StateRange=State),--(PercentageCompleteRange=PercentageCom plete)) Hope this helps! In article , Bahareh wrote: Hello There is a rutine counting each week (very boring) I want to make it authomatic. I have an spread sheet which 4 columns are important: city, city status (On/Semi), state, %complete I want this counting perocess: 1- I want to count: How many cities in each state are in "On" status? 2-I want to count: How many cities in each state are "100%" complete? Please Help me to get ride of this boring manual updating. Thanks BHR |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count & If fomula
In article ,
Bahareh wrote: Thanks Domenic You're very welcome! ...but still I don`t know how to put 100% for second question Remove the quotes... =SUMPRODUCT(--('Completed Cities'!G4:G400="IL"),--('Completed Cities'!P4:P400100%)) ...and If it is possible for 1st question to add another condition as: How many cities in each state are "on" or "pending" status =SUMPRODUCT(--('Completed Cities'!G4:G400="IL"),--ISNUMBER(MATCH('Completed Cities'!C4:C400,{"on","pending"},0))) Hope this helps! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count & If fomula
Great! thanks!
"Domenic" wrote: In article , Bahareh wrote: Thanks Domenic You're very welcome! ...but still I don`t know how to put 100% for second question Remove the quotes... =SUMPRODUCT(--('Completed Cities'!G4:G400="IL"),--('Completed Cities'!P4:P400100%)) ...and If it is possible for 1st question to add another condition as: How many cities in each state are "on" or "pending" status =SUMPRODUCT(--('Completed Cities'!G4:G400="IL"),--ISNUMBER(MATCH('Completed Cities'!C4:C400,{"on","pending"},0))) Hope this helps! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fomula (if?) | Excel Worksheet Functions | |||
Fomula | Excel Discussion (Misc queries) | |||
Please help, i need a fomula | Excel Worksheet Functions | |||
IF Fomula | Excel Discussion (Misc queries) | |||
To get fomula to display a Zero | Excel Discussion (Misc queries) |