Excel 2003 count if multple conditions apply
 

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?
--
Josie

Excel 2003 count if multple conditions apply
 

Josie;193327 Wrote:
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?
--
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
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=53292

Excel 2003 count if multple conditions apply
 

Thanks for that. it worked brilliantly
--
Josie


"Pecoflyer" wrote:


Josie;193327 Wrote:
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?
--
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
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=53292