SUM if for separate sheet

[Milan77]

Hi people,
I a new here and i have a worksheet with several buyers in sheets. I need to hv one sheet in which i have for evry byer how much certai goods did he take. Crteria is month, type of goods. I am stuck on SUM IF formula which is giving zero or VALUE error
Here is my formula
SUM(IF(AND(MONTH('NAFTA AD-PUT NS-1'!$B$10:$B$500)=4;('NAFTA AD-PUT NS-1'!$D$10:$D$500)="BIT 60");('NAFTA AD-PUT NS-1'!$F$10:$F$500);0))

 

[drsarao]

Welcome to MrExcel!
This looks like an array formula.
It should be entered with Ctrl+Shift+Enter rather than just Enter.
Are you doing it that way?
If yes, then is 'NAFTA AD-PUT NS-1' sheet in the same workbook?
Are you expecting a Sum > 0 from the data?

 

[Milan77]

Welcome to MrExcel!
This looks like an array formula.
It should be entered with Ctrl+Shift+Enter rather than just Enter.
Are you doing it that way?
If yes, then is 'NAFTA AD-PUT NS-1' sheet in the same workbook?
Are you expecting a Sum > 0 from the data?

Yes, i am doing it in that way CTRL+SHIFT+ENTER.afa A-PUT NS-1 is in th sameworkbook and es i am expecting sum >0

 

[drsarao]

Didn't check the whole formula! IF() valuetrue arg is missing.
Try this:
SUM(IF(AND(MONTH('NAFTA AD-PUT NS-1'!$B$10:$B$500)=4;('NAFTA AD-PUT NS-1'!$D$10:$D$500)="BIT 60");('NAFTA AD-PUT NS-1'!$F$10:$F$500);1;0))

 

[Milan77]

Didn't check the whole formula! IF() valuetrue arg is missing.
Try this:
SUM(IF(AND(MONTH('NAFTA AD-PUT NS-1'!$B$10:$B$500)=4;('NAFTA AD-PUT NS-1'!$D$10:$D$500)="BIT 60");('NAFTA AD-PUT NS-1'!$F$10:$F$500);1;0))

Nope, says to many arguments

 

[drsarao]

This:
=SUM(IF(AND(MONTH('NAFTA AD-PUT NS-1'!$B$10:$B$500)=4;('NAFTA AD-PUT NS-1'!$D$10:$D$500)="BIT 60";('NAFTA AD-PUT NS-1'!$F$10:$F$500));1;0))

Shifted a bracket after "Bit 60" to end of AND()

 

[Milan77]

This:
=SUM(IF(AND(MONTH('NAFTA AD-PUT NS-1'!$B$10:$B$500)=4;('NAFTA AD-PUT NS-1'!$D$10:$D$500)="BIT 60";('NAFTA AD-PUT NS-1'!$F$10:$F$500));1;0))

Shifted a bracket after "Bit 60" to end of AND()

I think we are going in a wrong direction. the formula should do the following:
if month is 4 (APRIL), and if columns D10:D500 are BIT 60 then SUM F10:F500 otherwise =0. BIT 60 is the name of the goods sold.
I think your solution would give 1 or 0. Nevertheless it doesnt work, it is giving 0