Vectorization within functions (argument for conditional in SUMIF)

Question

This would be trivial in C++, Python, R, etc . But Excel is doing my head in. There are zillions of essentially the same simple example over and over on the web; I can't find anything to address this application of SUMIF/COUNTIF.

I have a list of items, with a category bit-flag to tag each item:

 ...... A ............. B .... C
1 |Item ........... # ... Flag
2 | Apples ....... 20 ... 1
3 | Oranges .... 30 ... 3
4 | Lemon ........40 ... 7
5 | School Bus .. 5 ... 4

Here, using the EXCEL function BITAND:

-- BITAND( < flag >, 1) is non-zero for fruit, 
-- BITAND( < flag >, 2) is non-zero for citrus,
-- BITAND( < flag >, 4) is non-zero for yellow,

I need to do something like:

Total Citrus = SUMIF(C2:C5, BITAND(C2:C5, 2) > 0, B2:B5)

This doesn't work because the conditional (2nd argument) can only use an implicit reference to the list elements in a simple conditional (e.g. ">5")

A variation might look like:

Total Citrus = SUM( IF(BITAND(C2:C5, 2) > 0, B2:B5) )

This almost works, but the conditional only tests against cell C2, and then sums the total list, or not.

Of course I could use three different columns and fill it with 1s and 0s, but that would be awful when scaled for my actual situation.

Answer 1

You have created an array formula¹ with the BITAND function². These require finalizing with Ctrl+Shift+Enter↵ rather than simply Enter↵. Once entered correctly, Excel will add braces around the formula (e.g. { and } ). You do not type these in yourself.

In E7 as,

=SUM(IF(SIGN(BITAND($C$2:$C$5, VLOOKUP(D7, $G$2:$H$5, 2, FALSE))), $B$2:$B$5))

Once it is entered correctly, fill down as necessary.

        

---

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

² _{The BITAND function² was introduced with Excel 2013. It is not available in earlier versions.}