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