SUMIF i think

[ExcelRoy]

Hi all,

I am looking for a formula to calculate totals based on codes

I have the following formula

=IF(COUNTBLANK(W16:W411)=396,"",SUMIF(AV16:AV411,{"Can","Cus","Man","Not","Obs","Ret","Rep","Sto","Sup"},W16:W411))

which calculates everything I need but I would only like it to calculate if there is a "B" in column range W16:W411

Many thanks

 

[ExcelMercy]

What about an if(or() leading your formula to check for B?

 

[Brian from Maui]

A little confusing. You want to trigger the formula if W16:W411 has 396 blanks, and the same range if there's a "B" which is also the range to sum? There's only 396 cells in that range. So, if there's 396 blank cells to trigger the SUM formula, there would be no data in the range.

Or am I missing something?

 

[bschwartz]

A little confusing. You want to trigger the formula if W16:W411 has 396 blanks, and the same range if there's a "B" which is also the range to sum? There's only 396 cells in that range. So, if there's 396 blank cells to trigger the SUM formula, there would be no data in the range.

Or am I missing something?

You are and you're not. He is triggering the formula when there is NOT 396 blanks in the range W16:W411.

=IF(COUNTBLANK(W16:W411)=396,"",SUMIF(AV16:AV411,{"Can","Cus","Man","Not","Obs","Ret","Rep","Sto","Sup"},W16:W411))

On the other hand, if they are all blank, the SUMIF will simply return 0, so it does seem somewhat unnecessary to have that IF in there. Unless for some reason a "" is preferable to a 0.

 

[ExcelRoy]

Hi all,

Thanks for the replies. I do prefer a "" to a 0 for presentation purposes

I would like the SUMIF to calculate all {"Can","Cus","Man","Not","Obs","Ret","Rep","Sto","Sup"} if column W16:W411 has a "B" in the range

Thanks

 

[Aladin Akyurek]

Hi all,

Thanks for the replies. I do prefer a "" to a 0 for presentation purposes

I would like the SUMIF to calculate all {"Can","Cus","Man","Not","Obs","Ret","Rep","Sto","Sup"} if column W16:W411 has a "B" in the range

Thanks

Care to post the data of the relevant ranges along with the expected outcome?

 

[ExcelRoy]

Hi just had somewhat of a brainstorm and I think I got it

=IF(COUNTBLANK(W16:W411)=396,"",SUMIF(AV16:AV411,{"Can","Cus","Man","Not","Obs","Ret","Rep","Sto","Sup"},W16:W411)*AND(SUMIF(AK16:AK411,"B",W16:W411)))

Can you please run through the logic as I think this will work?

Thanks all

 

[Aladin Akyurek]

Hi just had somewhat of a brainstorm and I think I got it

=IF(COUNTBLANK(W16:W411)=396,"",SUMIF(AV16:AV411,{"Can","Cus","Man","Not","Obs","Ret","Rep","Sto","Sup"},W16:W411)*AND(SUMIF(AK16:AK411,"B",W16:W411)))

Can you please run through the logic as I think this will work?

Thanks all

I think you should seriously consider the proposal of post #6.

 

[Brian from Maui]

I think you should seriously consider the proposal of post #6.

For what it's worth, try........

=SUMPRODUCT(--ISNUMBER(MATCH(AV16:AV411,B1:B9,0)),--(AK16:AK411="B"),W16:W411)

Where B1:B9 houses your criteria.