Countif Formula with Multiple Criteria / Sumif?

[DebbyH]

Hello, I have a several worksheets that I am trying to compile summary data into.
Here is an example of one of the sheets that will feed into the summary page. Departments A, B, and C, fall into Category 1. Department D is Category 2, Department E is Category 3.

Item # Dept# Seller Revenue
1 A JD $100
2 A JD $75
3 B JD $10
4 C JD $30
5 D JD $200
6 E JD $300
7 B JD $10
8 F JD $600
9 C JD $10

In a separate sheet, I need to create a summary that COUNTS the number of occurrences for Cat 1, 2, 3, ..... and there respective revenue

Seller Cat 1 Cat 1 Rev Cat 2 Cat 2 Rev Cat 3 Cat 3 Rev
JD 6 $225 1 $200 1 $300


I was thinking a countif(b1:b10, "A") + Countif(b1:b10:, "B") + ...... for the count of the department, but not sure how to count for the revenue.

Anyway, help is needed.

Thank you in advance.

 

[Michael_E]

I would add three columns to your non summary worksheet with these formulas,

=IF(B2="A",D2,IF(B2="B",D2,IF(B2="C",0))) (In E)
=IF(B2="D",D2,0) (In F)
=IF(B2="E",D2,0) (In G)

and then for your Cat 1 Rev on your summary, just =SUM(E1:E10), Cat 2 =SUM(F1:F10), Cat 3 = SUM(G1:G10). If you don't like the extra columns being visible they can be hidden and still function.

 

[JackDanIce]

Have you considered using a SUMIF or SUMIFs function?
https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b

 

[Michael_E]

or just use SUMIF, for the third argument in the formula you can just put in the range of numbers you want to add and it works SUMIF(b1:b10, "A",D1:D10) etc.

 

[Joyner]

Try something like this:

For the count Cat 1:


=SUMPRODUCT(--($A$2:$A$10={"A","B","C"}))

For the SUM Cat 1:

=SUMPRODUCT(--($A$2:$A$10={"A","B","C"})*$C$2:$C$10)

For the others with only one Department, you could use the same or a simple COUNTIFS or SUMIFS like:

=COUNTIF($A$2:$A$10,"D") and =SUMIF($A$2:$A$10,"D",C2:C10)

If you can add a column to specify the Category, you could use a simple COUNTIF or COUNTIFS and SUMIF/SUMIFS for the formula.