Countifs and/or Sumproduct Not Working
I have tried every possible version of countif/countifs/sumproducts, etc to count the cells with the following criteria and all I get are errors or #VALUE!
I am at my breaking point. All I want is excel to count the cells that meet the following criteria. The below is the last formula I tried. This should be simple but excel is not making it simple. PLEASE HELP!
=SUMPRODUCT(Sheet1!AT3="1")*(Sheet1!AQ3<>"")*(Sheet1!AQ3<>"Only")*(Sheet1!AQ3<>"No")*(Sheet1!AQ3<>"Fitness")
Greatly Appreciated!
Try another pair of brackets.
=SUMPRODUCT((Sheet1!AT3="1")*(Sheet1!AQ3<>"")*(Sheet1!AQ3<>"Only")*(Sheet1!AQ3<>"No")*(Sheet1!AQ3<>"Fitness"))
and is the Sheet1!AT3 value a text "1" or an actual number 1. If the latter remove the "".
Dave
The 1 is the latter, duh to me. Added the extra brackets. Making all those changes gave me a result of 871 which is better than what I had....but the result should be 841. I did AT3 and AQ3 as a test which did work but when I changed to count all cells in the two columns (AT3:AT1700 and AQ3:AQ1700) I got a total of 871 but should have been 841.
I realized the "Only" is a custom number format of the #5 from a pivot table, the "No" is a #3 custom number format from a pivot table ad the "Fitness" is a #6 custom number format from a pivot table. So I changed the words to numbers to see if that would make a difference and received a total of 881. Still not the 841.
In other,words, I created a pivot table with the information for this spreadsheet. Because pivot tables don't allow wording, I had to create a custom number format so that 5=Only, 3=No, and 6=Fitness. This is then transported to the excel spreadsheet. I tried the verbiage (ie: "No",etc) and received a result of 871. Then I tried the number (ie: 3,etc) and received a result of 881. Neither of which are giving me the actual result of 841.
Then I removed the formulas in sheet1!AT thinking the custom format was causing the issue and still received a total of 871 not the 841 I need.
It is getting closer.
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
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
It is not allowing me to attach the dummy report. As soon as I can I will attach.
Sheet2 defines the search. I had to add one more criteria so the total should be 739. I removed the custom number format. The entire sheet1 will not have any formulas.
Last edited by srsev; 06-27-2019 at 09:32 AM.
I was able to attach the dummy report in my previous reply....yay!
841 Definitely
Why would you want 739 as indicated on sheet2 ?
When I added another criteria it brought the number down. But I was able to finally get it to work properly. So the first response from FlameRetired with the easy fixes helped out a lot to get it done and working properly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks