sumif with multiple conditions

[Ray]

I have three columns: a, b & c and need sum the figures on c if a = week1
and b = empty. Can someone suggest a formula to resolve this issue.

Thanks,

Ray

[KL]

=SUMPRODUCT(--(A1:A100="week1"),--(B1:B100=""),C1:C100)

or a little slower:

=SUMPRODUCT((A1:A100="week1")*(B1:B100="")*C1:C100)

Regards,
KL


"Ray" <[email protected]> wrote in message
news:[email protected]...
>I have three columns: a, b & c and need sum the figures on c if a = week1
>and b = empty. Can someone suggest a formula to resolve this issue.
>
> Thanks,
>
> Ray
>

[Ray]

KL,

Thanks for your useful suggestion. Is it possible to apply to countif
function as well?

Thanks,

Ray

"KL" <[email protected]> wrote in message
news:%[email protected]...
> =SUMPRODUCT(--(A1:A100="week1"),--(B1:B100=""),C1:C100)
>
> or a little slower:
>
> =SUMPRODUCT((A1:A100="week1")*(B1:B100="")*C1:C100)
>
> Regards,
> KL
>
>
> "Ray" <[email protected]> wrote in message
> news:[email protected]...
>>I have three columns: a, b & c and need sum the figures on c if a = week1
>>and b = empty. Can someone suggest a formula to resolve this issue.
>>
>> Thanks,
>>
>> Ray
>>
>
>

[Bob Phillips]

=SUMPRODUCT((A1:A100="week1")*(B1:B100=""))


--
HTH

Bob Phillips

"Ray" <[email protected]> wrote in message
news:%[email protected]...
> KL,
>
> Thanks for your useful suggestion. Is it possible to apply to countif
> function as well?
>
> Thanks,
>
> Ray
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
> > =SUMPRODUCT(--(A1:A100="week1"),--(B1:B100=""),C1:C100)
> >
> > or a little slower:
> >
> > =SUMPRODUCT((A1:A100="week1")*(B1:B100="")*C1:C100)
> >
> > Regards,
> > KL
> >
> >
> > "Ray" <[email protected]> wrote in message
> > news:[email protected]...
> >>I have three columns: a, b & c and need sum the figures on c if a =
week1
> >>and b = empty. Can someone suggest a formula to resolve this issue.
> >>
> >> Thanks,
> >>
> >> Ray
> >>
> >
> >
>
>

[KL]

I guess you meant SUMIF. Don't think so, SUMIF takes only 3 arguments.
Regards,
KL


"Ray" <[email protected]> wrote in message
news:%[email protected]...
> KL,
>
> Thanks for your useful suggestion. Is it possible to apply to countif
> function as well?
>
> Thanks,
>
> Ray
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> =SUMPRODUCT(--(A1:A100="week1"),--(B1:B100=""),C1:C100)
>>
>> or a little slower:
>>
>> =SUMPRODUCT((A1:A100="week1")*(B1:B100="")*C1:C100)
>>
>> Regards,
>> KL
>>
>>
>> "Ray" <[email protected]> wrote in message
>> news:[email protected]...
>>>I have three columns: a, b & c and need sum the figures on c if a = week1
>>>and b = empty. Can someone suggest a formula to resolve this issue.
>>>
>>> Thanks,
>>>
>>> Ray
>>>
>>
>>
>
>

[Ray]

KL,

No, I mean the same syntax is applied to countif. It seems not workable!

Ray

"KL" <[email protected]> wrote in message
news:[email protected]...
>I guess you meant SUMIF. Don't think so, SUMIF takes only 3 arguments.
> Regards,
> KL
>
>
> "Ray" <[email protected]> wrote in message
> news:%[email protected]...
>> KL,
>>
>> Thanks for your useful suggestion. Is it possible to apply to countif
>> function as well?
>>
>> Thanks,
>>
>> Ray
>>
>> "KL" <[email protected]> wrote in message
>> news:%[email protected]...
>>> =SUMPRODUCT(--(A1:A100="week1"),--(B1:B100=""),C1:C100)
>>>
>>> or a little slower:
>>>
>>> =SUMPRODUCT((A1:A100="week1")*(B1:B100="")*C1:C100)
>>>
>>> Regards,
>>> KL
>>>
>>>
>>> "Ray" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>I have three columns: a, b & c and need sum the figures on c if a =
>>>>week1 and b = empty. Can someone suggest a formula to resolve this
>>>>issue.
>>>>
>>>> Thanks,
>>>>
>>>> Ray
>>>>
>>>
>>>
>>
>>
>
>

[Domenic]

Is this what you mean?

=SUMPRODUCT(--(A1:A100="week1"),--(B1:B100=""))

In article <#[email protected]>,
"Ray" <[email protected]> wrote:

> KL,
>
> Thanks for your useful suggestion. Is it possible to apply to countif
> function as well?
>
> Thanks,
>
> Ray

[KL]

Ray,

I guess Bob and Domenic have answered your question ;-) It is workable.

Regards,
KL


"Ray" <[email protected]> wrote in message
news:[email protected]...
> KL,
>
> No, I mean the same syntax is applied to countif. It seems not workable!
>
> Ray
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>>I guess you meant SUMIF. Don't think so, SUMIF takes only 3 arguments.
>> Regards,
>> KL
>>
>>
>> "Ray" <[email protected]> wrote in message
>> news:%[email protected]...
>>> KL,
>>>
>>> Thanks for your useful suggestion. Is it possible to apply to countif
>>> function as well?
>>>
>>> Thanks,
>>>
>>> Ray
>>>
>>> "KL" <[email protected]> wrote in message
>>> news:%[email protected]...
>>>> =SUMPRODUCT(--(A1:A100="week1"),--(B1:B100=""),C1:C100)
>>>>
>>>> or a little slower:
>>>>
>>>> =SUMPRODUCT((A1:A100="week1")*(B1:B100="")*C1:C100)
>>>>
>>>> Regards,
>>>> KL
>>>>
>>>>
>>>> "Ray" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>>I have three columns: a, b & c and need sum the figures on c if a =
>>>>>week1 and b = empty. Can someone suggest a formula to resolve this
>>>>>issue.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Ray
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Ray]

Thanks for all useful suggestions!

Ray

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Is this what you mean?
>
> =SUMPRODUCT(--(A1:A100="week1"),--(B1:B100=""))
>
> In article <#[email protected]>,
> "Ray" <[email protected]> wrote:
>
>> KL,
>>
>> Thanks for your useful suggestion. Is it possible to apply to countif
>> function as well?
>>
>> Thanks,
>>
>> Ray