im back! Countif with 3 criteria.

[jaxisdex]

Hey guys, im stuck once more. i know i know, huge surprise! im trying to do a count of cells that fall within in a certain range while having the correct status. Ex:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Order Amt[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]$210[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]$700[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]$579[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]$600[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]$999[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]$175[/TD]
[/TR]
</tbody>[/TABLE]

So I have been trying to come up with some way to count the amount of orders that fall within a range while having the correct status. If it is a type A C or D and falls within $0-$299 then type A C or D within $300-$499 and so on and so on. it is always types A C or D but they need to be grouped as one.
=SUMPRODUCT(('All FP Data'!S:S>=Legend!L5)*('All FP Data'!S:S<=Legend!M5)) the legendL5=0 while LegendM5=299. I couldnt figure out how to add an if statement to accomplish this. If anyone could lend me a hand again i would and am really appreciative or your guys skills! If this is an ineffective way, please feel free to call me an idiot and tell me how to do it better! but it has to be a function formula, no VBA. This is a report that is updated weekly so im looking for a way to auto update.

Please and thank you,
Jaxs

 

[jarjarbingie]

=SUMPRODUCT(('All FP Data'!S2:S9>=L5)*('All FP Data'!S2:S9<=M5)*(('All FP Data'!R2:R9="A")+('All FP Data'!R2:R9="C")+('All FP Data'!R2:R9="D")))

Hey guys, im stuck once more. i know i know, huge surprise! im trying to do a count of cells that fall within in a certain range while having the correct status. Ex:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Order Amt[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]$210[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]$700[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]$579[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]$600[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]$999[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]$175[/TD]
[/TR]
</tbody>[/TABLE]

So I have been trying to come up with some way to count the amount of orders that fall within a range while having the correct status. If it is a type A C or D and falls within $0-$299 then type A C or D within $300-$499 and so on and so on. it is always types A C or D but they need to be grouped as one.
=SUMPRODUCT(('All FP Data'!S:S>=Legend!L5)*('All FP Data'!S:S<=Legend!M5)) the legendL5=0 while LegendM5=299. I couldnt figure out how to add an if statement to accomplish this. If anyone could lend me a hand again i would and am really appreciative or your guys skills! If this is an ineffective way, please feel free to call me an idiot and tell me how to do it better! but it has to be a function formula, no VBA. This is a report that is updated weekly so im looking for a way to auto update.

Please and thank you,
Jaxs

 

[jaxisdex]

Hmm that keeps coming up with 0. this is how i ended up writing it:

=SUMPRODUCT('All FP Data'!S2:S2066>=Legend!L2)*('All FP Data'!S2:S2066<=Legend!M2)*(('All FP Data'!R2:R2066="B")+('All FP Data'!R2:R2066="C")+('All FP Data'!R2:R2066="G")+('All FP Data'!R2:R2066="J")+('All FP Data'!R2:R2066="N"))

thank you for trying tho!

 

[jaxisdex]

=COUNTIFS('All FP Data'!S:S,">=1",'All FP Data'!S:S,"<=299.99",'All FP Data'!R:R,"=B") and so on for each letter and range.

never mind, i ended up just creating a separate countif for all of the 5 different status, then just did a sum function to add them up. Thank you for helping me figure out how to write the criteria!