# SUMIF i think

#### ExcelRoy

##### Well-known Member
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Brian from Maui

##### MrExcel MVP
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

##### Well-known Member
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

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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

##### MrExcel MVP
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)

Replies
5
Views
373
Replies
1
Views
205
Replies
4
Views
590
Replies
3
Views
280
Replies
23
Views
2K

1,191,516
Messages
5,987,038
Members
440,074
Latest member
Emmanuelian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back