countif using multiple criteria

[Scott Kieta]

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$47>0)))
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.

 

[Ashish Mathur]

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$47>0)))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Peo Sjoblom]

Did you get that formula to work?

--


Regards,


Peo Sjoblom

 

[Scott Kieta]

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

 

[Peo Sjoblom]

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

 

[Peo Sjoblom]

What is it that you want to count?

--


Regards,


Peo Sjoblom

 

[Scott Kieta]

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