Hi,
I have searched the whole forum for a solution to the following issue, I am struggling to deal with, without any success.
I have a column A2:A300000 --> Months (January)
I have a column B2:B300000 --> Names (Characters)
I have a column AB2:AB300000 --> Items (Numbers)
I have a column I2:I9000 --> Locations (names include both Numbers, Text and the character #)
I want to calculate an average by counting the locations based on the defined month and name. Because One name maybe have more than one locations.
=SUMPRODUCT(--('RAW Data'!$A$2:$A$300000=A16),--('RAW Data'!$B$2:$B$300000=$B$17),--('RAW Data'!$AB$2:$AB$300000))/COUNTIFS('RAW Data'!$A$2:$A$300000,A16,'RAW Data'!$B$2:$B$300000,$B$17,'RAW Data'!$I2:$I300000,++++it needs criteria here but I do not know what to include since there are no available criteria eg. B18 or B19+++++)
Any help please? I have looked on internet and I couldnt find anything because most users use defined criteria according to a specific text they are looking to count and the function COUNTA doesnt work with multiple criteria. Lastly, the SUMPRODUCT in denominator does not work so I cant find a way to fix my spreadsheet. Thank you very much in advance
Bookmarks