Excel 2003 count if multple conditions apply

[Josie]

How do I calculate the following:

In column A I have the names of a number of suppliers, B contains the
budgeted cost of work, C contains invoiced value, D contains formula
displaying "over "or "under" budget. This information transfers to a summary
table for the month showing supplier and total value of work. i want to add
a column to the summary table showing the number of jobs that went over
budget for each supplier. I have been trying to use the countif function but
I get an error every time? Also is it possible to show the number of jobs
over budget for each supplier as a percentage?

 

[Pecoflyer]

How do I calculate the following:

In column A I have the names of a number of suppliers, B contains the
budgeted cost of work, C contains invoiced value, D contains formula
displaying "over "or "under" budget. This information transfers to
summary
table for the month showing supplier and total value of work. i wan
to add
a column to the summary table showing the number of jobs that wen
over
budget for each supplier. I have been trying to use the counti
function but
I get an error every time? Also is it possible to show the number o
jobs
over budget for each supplier as a percentage?

Hi,
to count the suppliers over budget use
=sumproduct(--(a1:a100="supplier_name")*--(d1:d100="over"))

Percentage will be obtained wit
=sumproduct(--(a1:a100="supplier_name")*--(d1:d100="over"))/countif(d1:d100,"over")

Pull both down as needed and adapt ranges as needed ( they both shoul
have the same length)
PS being a member of this forum allows to post sheets. it is the
easier to help you and give example

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Josie]

Thanks for that. it worked brilliantly
--
Josie

Hi,
to count the suppliers over budget use
=sumproduct(--(a1:a100="supplier_name")*--(d1:d100="over"))

Percentage will be obtained with
=sumproduct(--(a1:a100="supplier_name")*--(d1:d100="over"))/countif(d1:d100,"over")

Pull both down as needed and adapt ranges as needed ( they both should
have the same length)
PS being a member of this forum allows to post sheets. it is then
easier to help you and give examples


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faster