I am an experienced Excel programmer and find myself frustrated that I am unable to find an easy solution to this as it must be a common requirement. I have been drawing a blank (no pun intended) all night and have eventually decided that there are bound to be much smarter people out there that will have an elegant solution. This question applies to a roster spreadsheet I have developed which transforms data input into a visually attractive format in first normal form that I then subsequently use for various lookups using SUMPRODUCT and dynamically named ranges . . . but it can be very simply stated as below:
The data above would have been the result of formulae, and in row 4 I have tried to show that the data is actually equal to "" which obviously shows up as a blank cell. What I would like to do simply is to use SUMPRODUCT to ideally calculate say the sum of all values in column A when column B is equal to Tue.
If the data in row 4 was zeroes I could easily use =SUMPRODUCT((B1:B5="Tue")*(A1:A5)) to get 8. Instead as I have the blank ("") data in row 4, I will get a #VALUE error instead.
I have tried a number of ways to get the "" converted to a numeric zero for the formula calculation without any success. The N formula would be ideal and works like a charm on individual cells but it does not operate on a range.
Any help would be massively appreciated.