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

=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" <markmcd@bigpond.net.au> wrote in message
news:1154006213_13393@sp6iad.superfeed.net...
> 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
=----

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" <bob.NGs@somewhere.com> wrote in message
news:ecGOkoYsGHA.3324@TK2MSFTNGP04.phx.gbl...
> =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" <markmcd@bigpond.net.au> wrote in message
> news:1154006213_13393@sp6iad.superfeed.net...
>> 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 =----

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" <markmcd@bigpond.net.au> wrote in message
news:1154037218_1613@sp6iad.superfeed.net...
> 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" <bob.NGs@somewhere.com> wrote in message
> news:ecGOkoYsGHA.3324@TK2MSFTNGP04.phx.gbl...
> > =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" <markmcd@bigpond.net.au> wrote in message
> > news:1154006213_13393@sp6iad.superfeed.net...
> >> 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
=----