Multiple IF statements not working

Hello Excel Guru's

I am trying to add two additional IF statement to my formula and excel is not liking it. Below is the formula I have written. Also attached is the excel sample in which the one for OB/GYN is working but the one for Adult Behavioral Health and Peds Behavioral Health is not. I could use a little help. Thanks again.



=SUMIFS('Reported Membership Data'!$E$5:$E$300,'Reported Membership Data'!$B$5:$B$300,$A$5,IF($A6="OB/GYN",'Reported Membership Data'!$G$5:$G$300,'Reported Membership Data'!$F$5:$F$300,IF($A6="Adult Behavioral Health",'Reported Membership Data'!$H$5:$H$300,'Reported Membership Data'!$F$5:$F$300,IF($A6="Peds Behavioral Health",'Reported Membership Data'!$H$5:$H$300,'Reported Membership Data'!$F$5:$F$300))),$A6,'Reported Membership Data'!$A$5:$A$300,">="&Membership!B$4,'Reported Membership Data'!$A$5:$A$300,"<="&EOMONTH(B$4,0))

Here is that whole formula and error


=SUMIFS('Reported Membership Data'!$E$5:$E$300,'Reported Membership Data'!$B$5:$B$300,$A$5,IF($A6="OB/GYN",'Reported Membership Data'!$G$5:$G$300,'Reported Membership Data'!$F$5:$F$300,IF($A6="Adult Behavioral Health",'Reported Membership Data'!$H$5:$H$300,'Reported Membership Data'!$F$5:$F$300,IF($A6="Peds Behavioral Health",'Reported Membership Data'!$H$5:$H$300,'Reported Membership Data'!$F$5:$F$300))),$A6,'Reported Membership Data'!$A$5:$A$300,">="&Membership!B$4,'Reported Membership Data'!$A$5:$A$300,"<="&EOMONTH(B$4,0))

broken down;

=SUMIFS('Reported Membership Data'!$E$5:$E$300,'Reported Membership Data'!$B$5:$B$300,$A$5,

IF($A6="OB/GYN", <-logical_test
'Reported Membership Data'!$G$5:$G$300, <- value if true
'Reported Membership Data'!$F$5:$F$300, < Value if false
The formula stops here and nothing else is processed

IF($A6="Adult Behavioral Health",'Reported Membership Data'!$H$5:$H$300,'Reported Membership Data'!$F$5:$F$300,IF($A6="Peds Behavioral Health",'Reported Membership Data'!$H$5:$H$300,'Reported Membership Data'!$F$5:$F$300))),$A6,'Reported Membership Data'!$A$5:$A$300,">="&Membership!B$4,'Reported Membership Data'!$A$5:$A$300,"<="&EOMONTH(B$4,0))

I thought that looked familiar

Try this...
=SUMIFS('Reported Membership Data'!$E$5:$E$300,
'Reported Membership Data'!$B$5:$B$300,$A$5,
IF($A6="OB/GYN", 'Reported Membership Data'!$G$5:$G$300, IF(RIGHT(A6,17)="Behavioral Health", 'Reported Membership Data'!$H$5:$H$300,'Reported Membership Data'!$F$5:$F$300)),$A6,'Reported Membership Data'!$A$5:$A$300,">="&Membership!B$4,'Reported Membership Data'!$A$5:$A$300,"<="&EOMONTH(B$4,0))

I didnt check all the values, but it looks like if you have adult in F, you also have Adult Behavioral Health in H? If thats the case, you could just sum on adult?