Satisfy 2 and more criteria
 

I have a situation where there needs to be 2 conditions satisfied.

In column A:A, there are a list of stores. 10 stores of one type and say 15
stores of another type although I have multiple store types. In column B:B
is the cost associated with each store. The objective here is to compare the
cost associated with each store this year vs last year for each store type
satisfying certain conditions. In column C:C is the bench data (last year's
data). Not all the stores have bench data to compare to so in that case I
have done a vlookup and if formula to state "No bench data" where this is
the case.

What I now need to do is for a particular store type, I want to count the
data only if it Store Type A, Store Type B etc and only for those sites that
have bench data and likewise the sum.

So for example if there are 10 stores all of type A but only 7 have bench
data from the prior year then I want the count to be 7 (not 10) and likewise
the sum for only those stores that have bench data from the prior year.

Any help most appreciated.

Mark



----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Satisfy 2 and more criteria
 

=SUMPRODUCT(--(A2:A20="Type A"),--(C2:C20<""))

and

=SUMIF(A:A,"Type A",C:C)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mark McDonough" wrote in message
...
I have a situation where there needs to be 2 conditions satisfied.

In column A:A, there are a list of stores. 10 stores of one type and say
15
stores of another type although I have multiple store types. In column B:B
is the cost associated with each store. The objective here is to compare
the
cost associated with each store this year vs last year for each store type
satisfying certain conditions. In column C:C is the bench data (last
year's
data). Not all the stores have bench data to compare to so in that case I
have done a vlookup and if formula to state "No bench data" where this is
the case.

What I now need to do is for a particular store type, I want to count the
data only if it Store Type A, Store Type B etc and only for those sites
that
have bench data and likewise the sum.

So for example if there are 10 stores all of type A but only 7 have bench
data from the prior year then I want the count to be 7 (not 10) and
likewise
the sum for only those stores that have bench data from the prior year.

Any help most appreciated.

Mark



----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

Satisfy 2 and more criteria
 

Hi Bob,

Thanks so much for this. I wish I had your knowledge.

Is there another solution using countif and sumif formulas. Others at work
will need to work this spreadsheet and they don't understand SUMPRODUCT or
even have heard of it.

I had a feeling someone would come back to me with the use of SUMPRODUCT
which is fine for me but not for them. I should probably have mentioned this
in the first place.

Cheers

Mark


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A2:A20="Type A"),--(C2:C20<""))

and

=SUMIF(A:A,"Type A",C:C)

Note that SUMPRODUCT doesn't work with complete columns, you have to
specify
a range.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mark McDonough" wrote in message
...
I have a situation where there needs to be 2 conditions satisfied.

In column A:A, there are a list of stores. 10 stores of one type and say
15
stores of another type although I have multiple store types. In column
B:B
is the cost associated with each store. The objective here is to compare
the
cost associated with each store this year vs last year for each store
type
satisfying certain conditions. In column C:C is the bench data (last
year's
data). Not all the stores have bench data to compare to so in that case I
have done a vlookup and if formula to state "No bench data" where this is
the case.

What I now need to do is for a particular store type, I want to count the
data only if it Store Type A, Store Type B etc and only for those sites
that
have bench data and likewise the sum.

So for example if there are 10 stores all of type A but only 7 have bench
data from the prior year then I want the count to be 7 (not 10) and
likewise
the sum for only those stores that have bench data from the prior year.

Any help most appreciated.

Mark



----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----





----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Satisfy 2 and more criteria
 

You could use SUM and IF in an array function

=SUM(IF((A2:A20="Type A")*(C2:C20<""),1,0))

or a straight SUM array formula

=SUM(--((A2:A20="Type A")*(C2:C20<"")0))

but I think this is less obvious than the SP.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mark McDonough" wrote in message
...
Hi Bob,

Thanks so much for this. I wish I had your knowledge.

Is there another solution using countif and sumif formulas. Others at work
will need to work this spreadsheet and they don't understand SUMPRODUCT or
even have heard of it.

I had a feeling someone would come back to me with the use of SUMPRODUCT
which is fine for me but not for them. I should probably have mentioned
this
in the first place.

Cheers

Mark


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A2:A20="Type A"),--(C2:C20<""))

and

=SUMIF(A:A,"Type A",C:C)

Note that SUMPRODUCT doesn't work with complete columns, you have to
specify
a range.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mark McDonough" wrote in message
...
I have a situation where there needs to be 2 conditions satisfied.

In column A:A, there are a list of stores. 10 stores of one type and
say
15
stores of another type although I have multiple store types. In column
B:B
is the cost associated with each store. The objective here is to
compare
the
cost associated with each store this year vs last year for each store
type
satisfying certain conditions. In column C:C is the bench data (last
year's
data). Not all the stores have bench data to compare to so in that case
I
have done a vlookup and if formula to state "No bench data" where this
is
the case.

What I now need to do is for a particular store type, I want to count
the
data only if it Store Type A, Store Type B etc and only for those sites
that
have bench data and likewise the sum.

So for example if there are 10 stores all of type A but only 7 have
bench
data from the prior year then I want the count to be 7 (not 10) and
likewise
the sum for only those stores that have bench data from the prior year.

Any help most appreciated.

Mark



----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World!
120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----





----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----