count & If fomula

[Bahareh]

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

 

[Domenic]

Try...

=SUMPRODUCT(--(StateRange=State),--(StatusRange=Status))

and

=SUMPRODUCT(--(StateRange=State),--(PercentageCompleteRange=PercentageCom
plete))

Hope this helps!

 

[Bahareh]

Thanks Domenic.

The first formula works well. but the second one doesn't. I put

=SUMPRODUCT(--('Completed Cities'!G4:G400="IL"),--('Completed
Cities'!P4400>"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

 

[Bahareh]

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]

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'!P4400>100%))

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

 

[Bahareh]

Great! thanks!

You're very welcome!


Remove the quotes...

=SUMPRODUCT(--('Completed Cities'!G4:G400="IL"),--('Completed
Cities'!P4400>100%))


=SUMPRODUCT(--('Completed
Cities'!G4:G400="IL"),--ISNUMBER(MATCH('Completed
Cities'!C4:C400,{"on","pending"},0)))

Hope this helps!