ADD A RANGE OF CELLS FROM MULTIPLE COLUMNS BASED ON CRITERIA FROM ONE COLUMN

[CampbellSoup]

Im looking to add the numbers from columns EL-EP based on criteria from column F. I'm trying to get a formula that will check column F for say "CC" and look through the criteria between EL5564-EP5568 and only calculate those figures in the line for "CC". hope I'm explaining it right.

I basically want to add up sales persons numbers and have the values in the graph below. But the numbers will span multiple columns and rows that are larger than the criteria established. I tried this formula here and its no good...Surely excel has a way to do this easily.

=SUMIF(F5564:F5568,"CC",EL5564-EP5568)

 

[Zot]

As column added and row increases, I think formula will gets longer and longer until annoying. Probably the easier way is to use a UDF

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

I tried this formula here and its no good...Surely excel has a way to do this easily.

=SUMIF(F5564:F5568,"CC",EL5564-EP5568)

Try

=SUMPRODUCT((F5564:F5568="CC")*EL5564:EP5568)