Excel formulae query

Question

I have a data sheet and have to create a report which is based on multiple conditions based on data. Attaching an image of the Data sheet and another sheer which is a report sheet.

Data Sheet:

Report Sheet:

Question: In the report sheet, in column A I have unique types and in column B I want the total count of the objects based on type and color. So presently I want total count of Orange fruit and Orange vegetable. i.e. B3 will have 2 as the value and B4 will be 1.

I tried the following formulae, but I am getting 0 in B3:

=COUNT(IF((Data!$A$2:Data!$A$7=A3 * Data!$B$2:Data!$B$7=“Orange”),Data!$C$2:Data!$C$7))

Is the condition I am using inside IF incorrect?

Answer 1

For getting the total, you are looking for SUM not COUNT.

Especially SUMIFS which as the name suggests, will let you define multiple criterias, like this:

=SUMIFS(C2:C7,A2:A7,"Fruits",B2:B7,"Orange") This will return 30.

=SUMIFS(C2:C7,A2:A7,"Vegetables",B2:B7,"Orange") And this will say 14.

So the first argument is the area where the numbers to be summed are; Next you will add the range where you would like to evaluate the following, the criteria.

Edit

I see that you might want to use COUNTIFS to return the number of occurences, not the total, in this case, this might be very similar:

=COUNTIFS(A2:A7,"Fruits",B2:B7,"Orange")

Answer 2

You can get it to work with your original formula if

(1) You put extra brackets as shown

(2) Use SUM instead of COUNT

(3) Enter it as an array formula using Ctrl-Shift-Enter

=SUM(IF((Data!$A$2:Data!$A$7=A3) * (Data!$B$2:Data!$B$7="Orange"),Data!$C$2:Data!$C$7))

so it appears with curly brackets round it.

I also had to re-type the quotation marks to get it to work.

=SUM((Data!$A$2:Data!$A$7=A3) * (Data!$B$2:Data!$B$7="Orange") * Data!$C$2:Data!$C$7)

is another way of doing it, also as an array formula.