How do I make the SUMPRODUCT formula in I8 count only the boxes that are checked in column B. Any assistance would be greatly appreciated.
Thanks,
Patrick
How do I make the SUMPRODUCT formula in I8 count only the boxes that are checked in column B. Any assistance would be greatly appreciated.
Thanks,
Patrick
Oops wrong cell reference, I1
You could have a cell link for each of the check boxes...
Right click on the checkbox--->Format control---->Cell link---> Type in the cell address (say C1).
You could then use..
=COUNTIF(C1:C31,TRUE)
See attached.
Life's a spreadsheet, Excel!
Say thanks, Click *
I know how link but if i want to combine SUMPRODUCT with the linking process is it possible? For example, if I have 10 boxes checked and I only want the formula to identify or count the boxes that are within the date range of 1 - 7 Aug. Can this ve done?
Yes just add the TRUE validation in your existing formula. Hence,
=SUMPRODUCT(--(A1:A31>=DATEVALUE("1-Aug-2012")),--(A1:A31<=DATEVALUE("7-Aug-2012")),--(C1:C31=TRUE))
See attached
Thanks, that's exactly what I need. But one more question, I found this formula on an internet search. Can you explain the purpose of the -- s?
Glad it helps!
The --s coerce the result of the conditional test (TRUE/FALSE) into their numeric equivalent (1/0) which can then be evaluated by the sumproduct formula.
See thread below
http://www.excelforum.com/excel-prog...umproduct.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks