SUMIF and AND help
 

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

SUMIF and AND help
 

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" wrote in
message ...

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

SUMIF and AND help
 

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

SUMIF and AND help
 

Thanks! SUMPRODUCT did the trick.


--
deacs
------------------------------------------------------------------------
deacs's Profile: http://www.excelforum.com/member.php...fo&userid=2321
View this thread: http://www.excelforum.com/showthread...hreadid=506845