SUMIF and AND help

[deacs]

Is it possible to use the SUMIF and AND functions together in one formula?

I have a list of vendors and the total cost associated with each. The vendors show up in the rows in multiple instances down column A. I would like to sum all the total costs in column X for each vendor. If this were all, I could use this formula =sumif(a1:a100,"=vendor name", x1:x100). However, I have one additional wrinkle in this. There are certain rows for each vendor that I do not want to include in my sum formula. I have noted with the letter "y" in column K for the rows that I am interested in and would like to skip the rows for each vendor that do not include the "y".

Any help to accomplish this will be greatly appreciated.

Here's a summary of where my data set up...

In column A, I have a list of vendors.
In column X, I have the total cost associated with column A.
In column K, I have noted with the letter "y" the vendors in column A that I want to include in the sum of the total costs in column X.

Please let me know if I am unclear.

[Ian]

I've not really thought this through, but a possibility springs to mind. In
a spare column put a formula like =IF(K1="y",A1,"")
This will put the vendor name in this new column, only if column K is "y".
You can then use your original formula, but referencing the new column
instead of A.

--
Ian
--
"deacs" <[email protected]> wrote in
message news:[email protected]...
>
> Is it possible to use the SUMIF and AND functions together in one
> formula?
>
> I have a list of vendors and the total cost associated with each. The
> vendors show up in the rows in multiple instances down column A. I
> would like to sum all the total costs in column X for each vendor. If
> this were all, I could use this formula =sumif(a1:a100,"=vendor name",
> x1:x100). However, I have one additional wrinkle in this. There are
> certain rows for each vendor that I do not want to include in my sum
> formula. I have noted with the letter "y" in column K for the rows that
> I am interested in and would like to skip the rows for each vendor that
> do not include the "y".
>
> Any help to accomplish this will be greatly appreciated.
>
> Here's a summary of where my data set up...
>
> In column A, I have a list of vendors.
> In column X, I have the total cost associated with column A.
> In column K, I have noted with the letter "y" the vendors in column A
> that I want to include in the sum of the total costs in column X.
>
> Please let me know if I am unclear.
>
>
> --
> deacs
> ------------------------------------------------------------------------
> deacs's Profile:
> http://www.excelforum.com/member.php...fo&userid=2321
> View this thread: http://www.excelforum.com/showthread...hreadid=506845
>

[Elkar]

Try this:

=SUMPRODUCT(--(X1:X100),--(K1:K100="y"),--(A1:A100="Vendor Name"))

HTH,
Elkar

"deacs" wrote:

>
> Is it possible to use the SUMIF and AND functions together in one
> formula?
>
> I have a list of vendors and the total cost associated with each. The
> vendors show up in the rows in multiple instances down column A. I
> would like to sum all the total costs in column X for each vendor. If
> this were all, I could use this formula =sumif(a1:a100,"=vendor name",
> x1:x100). However, I have one additional wrinkle in this. There are
> certain rows for each vendor that I do not want to include in my sum
> formula. I have noted with the letter "y" in column K for the rows that
> I am interested in and would like to skip the rows for each vendor that
> do not include the "y".
>
> Any help to accomplish this will be greatly appreciated.
>
> Here's a summary of where my data set up...
>
> In column A, I have a list of vendors.
> In column X, I have the total cost associated with column A.
> In column K, I have noted with the letter "y" the vendors in column A
> that I want to include in the sum of the total costs in column X.
>
> Please let me know if I am unclear.
>
>
> --
> deacs
> ------------------------------------------------------------------------
> deacs's Profile: http://www.excelforum.com/member.php...fo&userid=2321
> View this thread: http://www.excelforum.com/showthread...hreadid=506845
>
>

[deacs]

Thanks! SUMPRODUCT did the trick.