Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif using multiple criteria
I have a fairly complex spreadsheet which covers a full month of work
and in alternating columns i have total volume and Average processing time, per day. The columns look something like this: A B C D E F G H 27-Oct 28-Oct 29-Oct 30-Oct Tue Wed Thu Fri RECVD APT RECVD APT RECVD APT RECVD APT What i need to do is calculate a running average file per day total per process, but and i have figured out how to calculate the average ignoring zero values, but then that is not accurate as i only want the average of the days that have already passed and ignore the zero values on the weekend. My thought was to use a countif formula to count the cells which have data in recvd column and ignore the dates with zero value. Basically as of today there would be 12 working days but my formula counts 31. Here is the formula i tried. =IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6= Recvd", now i want to add the 2nd criteria only counting cells 0. I tried a sumproduct (below) but it comes back with #VALUE, value is wrong data type. =IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) row 47 is the row with the sum of the values. Sorry this was so long, but i am hoping to clear it up and wanted to provide a lot of detail. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif using multiple criteria
Hi,
I do not see a problem with the SUMPRODUCT() formula. However, I do not see your logic of the first part - are you trying to check for either of C47:BV47 being 0. If that is indeed the case, then please try the following: =IF(or(C47:BV47)="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Scott Kieta" wrote in message ... I have a fairly complex spreadsheet which covers a full month of work and in alternating columns i have total volume and Average processing time, per day. The columns look something like this: A B C D E F G H 27-Oct 28-Oct 29-Oct 30-Oct Tue Wed Thu Fri RECVD APT RECVD APT RECVD APT RECVD APT What i need to do is calculate a running average file per day total per process, but and i have figured out how to calculate the average ignoring zero values, but then that is not accurate as i only want the average of the days that have already passed and ignore the zero values on the weekend. My thought was to use a countif formula to count the cells which have data in recvd column and ignore the dates with zero value. Basically as of today there would be 12 working days but my formula counts 31. Here is the formula i tried. =IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6= Recvd", now i want to add the 2nd criteria only counting cells 0. I tried a sumproduct (below) but it comes back with #VALUE, value is wrong data type. =IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) row 47 is the row with the sum of the values. Sorry this was so long, but i am hoping to clear it up and wanted to provide a lot of detail. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif using multiple criteria
Did you get that formula to work?
-- Regards, Peo Sjoblom "Ashish Mathur" wrote in message ... Hi, I do not see a problem with the SUMPRODUCT() formula. However, I do not see your logic of the first part - are you trying to check for either of C47:BV47 being 0. If that is indeed the case, then please try the following: =IF(or(C47:BV47)="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Scott Kieta" wrote in message ... I have a fairly complex spreadsheet which covers a full month of work and in alternating columns i have total volume and Average processing time, per day. The columns look something like this: A B C D E F G H 27-Oct 28-Oct 29-Oct 30-Oct Tue Wed Thu Fri RECVD APT RECVD APT RECVD APT RECVD APT What i need to do is calculate a running average file per day total per process, but and i have figured out how to calculate the average ignoring zero values, but then that is not accurate as i only want the average of the days that have already passed and ignore the zero values on the weekend. My thought was to use a countif formula to count the cells which have data in recvd column and ignore the dates with zero value. Basically as of today there would be 12 working days but my formula counts 31. Here is the formula i tried. =IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6= Recvd", now i want to add the 2nd criteria only counting cells 0. I tried a sumproduct (below) but it comes back with #VALUE, value is wrong data type. =IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) row 47 is the row with the sum of the values. Sorry this was so long, but i am hoping to clear it up and wanted to provide a lot of detail. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif using multiple criteria
I added that so that the formula would ignore the blank cells, would that not
be necessary? After i posted this request i noticed that my range did not match (through BT on one portion and through BV on the other. i adjusted to both go through BV and it worked as an array. Thank you for your help "Ashish Mathur" wrote: Hi, I do not see a problem with the SUMPRODUCT() formula. However, I do not see your logic of the first part - are you trying to check for either of C47:BV47 being 0. If that is indeed the case, then please try the following: =IF(or(C47:BV47)="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Scott Kieta" wrote in message ... I have a fairly complex spreadsheet which covers a full month of work and in alternating columns i have total volume and Average processing time, per day. The columns look something like this: A B C D E F G H 27-Oct 28-Oct 29-Oct 30-Oct Tue Wed Thu Fri RECVD APT RECVD APT RECVD APT RECVD APT What i need to do is calculate a running average file per day total per process, but and i have figured out how to calculate the average ignoring zero values, but then that is not accurate as i only want the average of the days that have already passed and ignore the zero values on the weekend. My thought was to use a countif formula to count the cells which have data in recvd column and ignore the dates with zero value. Basically as of today there would be 12 working days but my formula counts 31. Here is the formula i tried. =IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6= Recvd", now i want to add the 2nd criteria only counting cells 0. I tried a sumproduct (below) but it comes back with #VALUE, value is wrong data type. =IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) row 47 is the row with the sum of the values. Sorry this was so long, but i am hoping to clear it up and wanted to provide a lot of detail. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif using multiple criteria
It's not clear what you are trying to do, what do C6:BV6 contain
and what's in C47:BV47? Why is C6 used as criteria? -- Regards, Peo Sjoblom "Scott Kieta" wrote in message ... I have a fairly complex spreadsheet which covers a full month of work and in alternating columns i have total volume and Average processing time, per day. The columns look something like this: A B C D E F G H 27-Oct 28-Oct 29-Oct 30-Oct Tue Wed Thu Fri RECVD APT RECVD APT RECVD APT RECVD APT What i need to do is calculate a running average file per day total per process, but and i have figured out how to calculate the average ignoring zero values, but then that is not accurate as i only want the average of the days that have already passed and ignore the zero values on the weekend. My thought was to use a countif formula to count the cells which have data in recvd column and ignore the dates with zero value. Basically as of today there would be 12 working days but my formula counts 31. Here is the formula i tried. =IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6= Recvd", now i want to add the 2nd criteria only counting cells 0. I tried a sumproduct (below) but it comes back with #VALUE, value is wrong data type. =IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) row 47 is the row with the sum of the values. Sorry this was so long, but i am hoping to clear it up and wanted to provide a lot of detail. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif using multiple criteria
What is it that you want to count?
-- Regards, Peo Sjoblom "Scott Kieta" wrote in message ... I added that so that the formula would ignore the blank cells, would that not be necessary? After i posted this request i noticed that my range did not match (through BT on one portion and through BV on the other. i adjusted to both go through BV and it worked as an array. Thank you for your help "Ashish Mathur" wrote: Hi, I do not see a problem with the SUMPRODUCT() formula. However, I do not see your logic of the first part - are you trying to check for either of C47:BV47 being 0. If that is indeed the case, then please try the following: =IF(or(C47:BV47)="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Scott Kieta" wrote in message ... I have a fairly complex spreadsheet which covers a full month of work and in alternating columns i have total volume and Average processing time, per day. The columns look something like this: A B C D E F G H 27-Oct 28-Oct 29-Oct 30-Oct Tue Wed Thu Fri RECVD APT RECVD APT RECVD APT RECVD APT What i need to do is calculate a running average file per day total per process, but and i have figured out how to calculate the average ignoring zero values, but then that is not accurate as i only want the average of the days that have already passed and ignore the zero values on the weekend. My thought was to use a countif formula to count the cells which have data in recvd column and ignore the dates with zero value. Basically as of today there would be 12 working days but my formula counts 31. Here is the formula i tried. =IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6= Recvd", now i want to add the 2nd criteria only counting cells 0. I tried a sumproduct (below) but it comes back with #VALUE, value is wrong data type. =IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) row 47 is the row with the sum of the values. Sorry this was so long, but i am hoping to clear it up and wanted to provide a lot of detail. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif using multiple criteria
I did get the formula to work, the C6 is the criteria of the columns
associated with the Recvd (as opposed to the APT) i was telling the formula to only count those columns in which recvd existed (this is a header for the column) C6:BV6 has Recvd and APT in alternating columns and C47:BV47 are the sum totals of those columns. Thanks "Peo Sjoblom" wrote: It's not clear what you are trying to do, what do C6:BV6 contain and what's in C47:BV47? Why is C6 used as criteria? -- Regards, Peo Sjoblom "Scott Kieta" wrote in message ... I have a fairly complex spreadsheet which covers a full month of work and in alternating columns i have total volume and Average processing time, per day. The columns look something like this: A B C D E F G H 27-Oct 28-Oct 29-Oct 30-Oct Tue Wed Thu Fri RECVD APT RECVD APT RECVD APT RECVD APT What i need to do is calculate a running average file per day total per process, but and i have figured out how to calculate the average ignoring zero values, but then that is not accurate as i only want the average of the days that have already passed and ignore the zero values on the weekend. My thought was to use a countif formula to count the cells which have data in recvd column and ignore the dates with zero value. Basically as of today there would be 12 working days but my formula counts 31. Here is the formula i tried. =IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6= Recvd", now i want to add the 2nd criteria only counting cells 0. I tried a sumproduct (below) but it comes back with #VALUE, value is wrong data type. =IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$470))) row 47 is the row with the sum of the values. Sorry this was so long, but i am hoping to clear it up and wanted to provide a lot of detail. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with multiple criteria | Excel Worksheet Functions | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
countif, multiple criteria... | Excel Worksheet Functions | |||
countif using multiple criteria | Excel Discussion (Misc queries) | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |