Satisfy 2 and more criteria

[Mark McDonough]

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

 

[Bob Phillips]

=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)

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

=----

 

[Mark McDonough]

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]

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)

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






----== 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

=----