Hi all
I've attached a copy of the excel file I am currently working on.
what I am trying to do is fill out the "Number of #'s" cells based on a date entered into L3
so in essence
If any cell in Column A are equal to the date specified in cell L3 then count any "5"'s under the headings Q1-Q8 and provide a total as a whole number
Thinking a sum or sumproduct should work fine but im not quite sure why it isnt
=SUM(A:A=L3)*(B:I="5")
tried that and it only looks at one cell in column A,
=SUMPRODUCT((A:A=L3)*(B:I="5"))
looks at the whole column of A but i'm missing something as I'm getting the dreaded #NUM result
Any help is greatly appreciated
Last edited by reynastus; 08-10-2010 at 01:21 AM. Reason: Solved
Hi
try =SUMPRODUCT(($A$4:$A$100=$L$3)*($B$4:$I$100=5)) and adjust range to suit
Entire columns ( like A:A) are not allowed in XL2003 ( OK with XL 2007)
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
Originally Posted by arthurbr
Thanks for that it works, had me stumped because the A:A search was working ok on one of the formulas in the sheet (Cell L6's formula to be exact being a =countif(A:A,L3).)
one other thing im trying to do with this sheet is to count the blank cells in the same range
i'm attempting to use
(cell L7)
=SUMPRODUCT(--($A$4:$A$2000=$L$3)*--($B$4:$I$2000=ISBLANK)
however this isnt working either
changed ISBLANK to "" and works now=SUMPRODUCT(--($A$4:$A$2000=$L$3)*--($B$4:$I$2000=ISBLANK)
however this isnt working either
so end product is
and i get a number of records that were not filled out=SUMPRODUCT((--($A$4:$A$2000=$L$3)*--($B$4:$I$2000="")/8)
Thanks for the help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks