software

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,quot;=vendor namequot;,
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 quot;yquot; 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 quot;yquot;.

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 quot;yquot; 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: www.excelforum.com/member.php...foamp;userid=2321
View this thread: www.excelforum.com/showthread...hreadid=506845I've not really thought this through, but a possibility springs to mind. In
a spare column put a formula like =IF(K1=quot;yquot;,A1,quot;quot;)
This will put the vendor name in this new column, only if column K is quot;yquot;.
You can then use your original formula, but referencing the new column
instead of A.

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

=SUMPRODUCT(--(X1:X100),--(K1:K100=quot;yquot;),--(A1:A100=quot;Vendor Namequot;))

HTH,
Elkar

quot;deacsquot; wrote:

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


Thanks! SUMPRODUCT did the trick.--
deacs
------------------------------------------------------------------------
deacs's Profile: www.excelforum.com/member.php...foamp;userid=2321
View this thread: www.excelforum.com/showthread...hreadid=506845