Solved i want to count the date in a forth array using sumproduct

January 14, 2014 at 23:56:57
Specs: Windows XP
This is the formula I have used
=SUMPRODUCT(('Engineer sheet'!$L3:$L$1005="ABC")*('Engineer sheet'!$H3:$H$1005="NORTH")*('Engineer sheet'!$G3:$G$1005="TABLE"))-COUNT('Engineer sheet'!O3:O1006)
The forth array in Column O is a range of dates, some are blank cells.
I am trying to total the number dates in in Column O based on the SUMPRODUCT from the previous 3 table arrays.

I use Excel 2003

Any help will be appricated
Regards Ken


See More: i want to count the date in a forth array using sumproduct

Report •


#1
January 15, 2014 at 11:39:30
re: "I am trying to total the number dates in in Column O based on the SUMPRODUCT from the previous 3 table arrays."

I'm not sure what you mean by "trying to total the number dates".

Are you trying to have the SUMPRODUCT only return a count of ABC, NORTH, TABLE if there is also a date in Column O?

As written, your formula will first calculate the number of rows where ABC, NORTH, TABLE are all present and then subtract the number of cells in Column O that contain any value. There is no relationship between the SUMPRODUCT part of your formula and the COUNT part. They are really 2 independent calculations.

What exactly are you trying to calculate?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 17, 2014 at 03:13:52
✔ Best Answer
Hi,
Thanks for time and effort. I wanted to count the dates in column P, when all the other 3 columns had the critrias mentioned

I have managed to to get the formual using
SUMPRODUCT(('Engineer sheet'!$L$2:$L$1005="abc")*('Engineer sheet'!$H$2:$H$1005="NORTH")*('Engineer sheet'!$G$2:$G$1005="abc")*('Engineer sheet'!$P$2:$P$1005<>0))


Report •
Related Solutions


Ask Question