Hi,
This is the first time i'm using sumproduct, but I can't understand where it goes wrong.
The point of these two formulas is to count number of people met and number of meetings.
The problem though is that sumproduct sometimes gives me more meetings held than number of persons met, which is impossible of course.
Column T is what specifies if a meeting has been held (there can be many people at a meeting, so in my data 1 represents a meeting as to differ meetings from persons met with)
Formula:
SUMPRODUCT(('Data'!$B$1:$B$8566=C$7)*('Data'!$AG$1:$AG$8566=$B153)*(('Data'!$AJ$1:$AJ$8566=$A$132)+('Data'!$AK$1:$AK$8566=$A$132)+('Data'!$AL$1:$AL$8566=$A$132)+('Data'!$AM$1:$AM$8566=$A$132)))
Formula:
SUMPRODUCT(('Data'!$B$1:$B$8566=C$7)*('Data'!$T$1:$T$8566=1)*('Data'!$AG$1:$AG$8566=$B153)*(('Data'!$AJ$1:$AJ$8566=$A$132)+('Data'!$AK$1:$AK$8566=$A$7)+('Data'!$AL$1:$AL$8566=$A$7)+('Data'!$AM$1:$AM$8566=$A$7)))
So the first formula is for number of persons met
The second formula is number of meetings.
In my data, more meetings are held than people met in a few instances. Mostly it seems to work, but these irregularities make me wonder if there is something wrong with the formula...
Bookmarks