# Thread: Sumif with formula as criteria (Excel 2003/SP2)

1. ## Sumif with formula as criteria (Excel 2003/SP2)

The goal here is a simplified pie chart. If categories are less than 10% of the total value they should be amalgamated into the category "Other".
When calculating the Other category I'm currently using the following formula:
=SUMIF(F2:F15,2,B2:B15) Where the value 2 is the type returned by the cells. Ideally, I'd like to include the type function in the criteria for the formula - like this =SUMIF(e2:e15,type(e2:e15)=2,B2:B15). However that formula doesn't work and neither does
{=SUM(IF(TYPE(E2:E15)=2,B2:B15))}

Obviously, my solution which requires type to be evaluated externally is working I would just find it more satisfying to place the criteria into the formula.
I've also run into the same problem when using the Month function as criteria.

Any thoughts?

2. ## Re: Sumif with formula as criteria (Excel 2003/SP2)

TYPE doesn't result in an array, but in a single value, hence you cannot use it. You can use the condition directly:
<code>
=SUMIF(B2:B15,"<"&GrandTotal/10,B2:B15)
</code>
or
<code>
=SUMPRODUCT((B2:B15<GrandTotal/10)*B2:B15)</code>

3. ## Re: Sumif with formula as criteria (Excel 2003/SP2

OOh, thanks for clearing up my tunnel vision!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•