Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How many conditions can I apply to a cell? I need to apply 8. Markus Excel Discussion (Misc queries) 2 July 10th 08 01:06 PM
How can I count data after I apply a Filter in MS Excel Smithcd3 Excel Worksheet Functions 1 January 10th 08 05:08 AM
searching text and conditional format with multple conditions diaare Excel Worksheet Functions 4 December 17th 07 09:12 PM
How Do I have excel count 2 cells with conditions (Excel 2003) Dave New Users to Excel 4 December 19th 05 07:37 PM
Summing if several conditions apply [email protected] Excel Worksheet Functions 3 December 1st 05 05:52 PM


All times are GMT +1. The time now is 12:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"