Hi,
I have attached a sample file that shows a SUMPRODUCT formula.
I am perplexed why I am getting an answer of 15 when I know there are 16 occurrences of the particular team mentioned in the formula.
Could someone please shine some light on what I am missing? Probably something very obvious and I have spent too much time looking at the formula?
Thank-you for any help offered.
Hi, to begin with are you sure you have more than 13 times the word Beaconsfield in column A?
A201 has a traling space - remove it and you get 16.
You can also use this...
=COUNTIFS(A:A,J3,D:D,"<=20",G:G,1)+COUNTIFS(C:C,J3,D:D,"<=20",G:G,1)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
@FDibbins. Could you tell why in the sorted range, after using advance filtering on the op's range, the function SUMPRODUCT doesn't return 16?
Actually, if you use regular filter...
filter D to exclude >20
filter G =1
filter A TO Beaconsfield
=6
Remove filter from A and apply to C
=10
Just checked your list w. a Pivottable. There are 3 occurrence of "Beaconsfield" with an extra charachter at the end. Remove it and your formula will work.
/Robert
i think that was answered there are trailing spaces in some of the cells remove them and you get the right answer
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thanks for the explanation. So SUMPRODUCT((A2:C333=J3) searches for "Beaconsfield" in column A AND in column C. Maybe advance filter might have a problem here because op gave the same headline - team - for both columns, A & C..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks