# SUMIF Criteria range to be taken automatically

1. ## SUMIF Criteria range to be taken automatically

Dear All,

Actually SUMIF function will be like SUMIF(Range, Criteria, SUM Range)

SO here I want range (in the sumif function) to be taken automatically rather than me selecting the particular range based on header

Ex: I'm using formula =SUMIF(Production!\$A\$37:\$AG\$45,Export!A2,Production!\$BL\$38:\$BL\$45) where as it is returning zero even there is a value for that particular item

2. ## Re: SUMIF Criteria range to be taken automatically

Criteria range and Sum range must have same length. Numbers may be in text format.

3. ## Re: SUMIF Criteria range to be taken automatically

Originally Posted by kvsrinivasamurthy
Criteria range and Sum range must have same length.
Not necessarily...

Data Range
 A B C D 1 X 10 30 2 Z 10 3 X 10 4 Y 10 5 X 10 6 ------ ------ ------ ------

This formula entered in D1:

=SUMIF(A1:A5,"X",B1)

4. ## Re: SUMIF Criteria range to be taken automatically

Originally Posted by Tony Valko
Not necessarily...

Data Range
 A B C D 1 X 10 30 2 Z 10 3 X 10 4 Y 10 5 X 10 6 ------ ------ ------ ------

This formula entered in D1:

=SUMIF(A1:A5,"X",B1)
But as per my formula it is becoming zero, I dont know why is it happening? Please clarify

5. ## Re: SUMIF Criteria range to be taken automatically

Originally Posted by laansesu
Ex: I'm using formula =SUMIF(Production!\$A\$37:\$AG\$45,Export!A2,Production!\$BL\$38:\$BL\$45) where as it is returning zero even there is a value for that particular item
Why the difference?

Regards

6. ## Re: SUMIF Criteria range to be taken automatically

Sorry I think I even considered Header also. After seeing your comment I've changed it to \$A\$38 where it is \$A\$37 but still ans is zero. I'm still not getting the accurate answer. Request you to please clarrify.

Regards

8. ## Re: SUMIF Criteria range to be taken automatically

PFA file and do the needful.

9. ## Re: SUMIF Criteria range to be taken automatically

Try ..

=SUMIF(Production!\$B\$38:\$B\$45,Export!A2,Production!\$BL\$38:\$BL\$45)

SO here I want range (in the sumif function) to be taken automatically rather than me selecting the particular range based on header
Not sure what you mean by the above

10. ## Re: SUMIF Criteria range to be taken automatically

Here what you are doing is, you are selecting the particular row (of Downrod i.e., B:B) where as I'm selecting all the rows (i.e., from A:AG) also so that I can use only one formula for populating the data in this present sheet

Can we do like that. Please confirm.

11. ## Re: SUMIF Criteria range to be taken automatically

try

=SUM(IF(Production!\$A\$38:\$AG\$45=Export!A2,Production!\$BL\$38:\$BL\$45))

Enter with Ctrl+Shift+Enter

You need to remove #N/A errors by putting IFERROR additions around your formulae for "Closing Stock"

12. ## Re: SUMIF Criteria range to be taken automatically

Originally Posted by JohnTopley
=SUM(IF(Production!\$A\$38:\$AG\$45=Export!A2,Production!\$BL\$38:\$BL\$45))
Just curious - what advantage does that have over the equivalent SUMIF construction?

Regards

13. ## Re: SUMIF Criteria range to be taken automatically

Originally Posted by JohnTopley
try

=SUM(IF(Production!\$A\$38:\$AG\$45=Export!A2,Production!\$BL\$38:\$BL\$45))

Enter with Ctrl+Shift+Enter

You need to remove #N/A errors by putting IFERROR additions around your formulae for "Closing Stock"

Thanks its working now!!!!

14. ## Re: SUMIF Criteria range to be taken automatically

@XOR LX

SUMIF (for me) did not work over a range i.e range which was not single column or single row: maybe I was doing something wrong?

Original SUMIF was

=SUMIF(Production!\$A\$38:\$AG\$45,Export!A2,Production!\$BL\$38:\$BL\$45))

@ Iaansesu

The #N/A are displaying because you have many #N/As in the data range.

I removed all the #N/A errors and retrieved the value 1000.

15. ## Re: SUMIF Criteria range to be taken automatically

Yes I got it!!! I've gone through the evaluation formula to check the issue. Now its working..I also changed this post as solved now.

16. ## Re: SUMIF Criteria range to be taken automatically

See attached.

17. ## Re: SUMIF Criteria range to be taken automatically

Glad it is solved!

18. ## Re: SUMIF Criteria range to be taken automatically

Originally Posted by JohnTopley
@XOR LX

SUMIF (for me) did not work over a range i.e range which was not single column or single row: maybe I was doing something wrong?
Apologies. You're perfectly correct. I didn't read the question properly.

Regards

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1