Sumifs with criteria as cell

[Dhouse87]

I am unable to post an example at this time so I'll try to explain it

=Sumif(A9:A15, B9:B15,">="B1,"<="B7)

In B1:B7 is 1st, 2nd, 3rd, 4th, 5th, 6th, 7th
In B9:B15 is that same number format.
Ive tried just doing the numbers and it still didnt calculate.

Ive tried ">=&B1" and still nothin.
Ive tried browsing through the sumifs date thread and couldnt find anything

 

[Marcelo Branco]

Try
=SUMIFS(A9:A15,B9:B15,">="&B1,B9:B15,"<="&B7)

M.

 

[Dhouse87]

I am unable to post an example at this time so I'll try to explain it

=Sumif(A9:A15, B9:B15,">="B1,"<="B7)

In B1:B7 is 1st, 2nd, 3rd, 4th, 5th, 6th, 7th
In B9:B15 is that same number format.
Ive tried just doing the numbers and it still didnt calculate.

Ive tried ">=&B1" and still nothin.
Ive tried browsing through the sumifs date thread and couldnt find anything

Also The criteria range has a formula within it too

 

[Alex Blakenburg]

Show us what you see in the formula bar of one of the cells in B9:B15 and also what is in the formula bar for B1.

 

[Dhouse87]

Show us what you see in the formula bar of one of the cells in B9:B15 and also what is in the formula bar for B1.

B9:15contains =if(F49>0, Sheet2!A4, "")
B1 contains 1st
The =SUMIFS(A9:A15,B9:B15,">="&B1,B9:B15,"<="&B7) worked for criteria range that dose not have a formula within it

 

[Alex Blakenburg]

Ok to B1 is text so >1st is unlikely to work. So what's in Sheet2!A4 ?

 

[Dhouse87]

Show us what you see in the formula bar of one of the cells in B9:B15 and also what is in the formula bar for B1.

B9:15contains =if(F49>0, Sheet2!A4, ""
B1 contains 1st

Ok to B1 is text so >1st is unlikely to work. So what's in Sheet2!A4 ?

That cell is blank blank for now but when that cell in sheet 2 is filled in it will fill others in

 

[Alex Blakenburg]

You might need to consider whether the presentation trumps functionality
If you put 1st 2nd 3rd you have entered the numbers as Text. Once you do that then using the comparative operators > & < no longer work.

 

[Dhouse87]

You might need to consider whether the presentation trumps functionality
If you put 1st 2nd 3rd you have entered the numbers as Text. Once you do that then using the comparative operators > & < no longer work.

Ive tried just using numbers and it still wont recognize

 

[Alex Blakenburg]

Both ranges need to have numbers in them for it to work.
One way of checking it is to change the Number format of both ranges from say General to Number with 2 decimal places. If nothing changes then they are text, if it changes to show 2 decimal places they are numbers and the formula should work.