Sumifs formula with integrated Index formula... I think.

Hello all,

Please see attached sheet, the explantion of my problem will be a lot clearer. Excel 2007.

There would be a easier solution, but the configuration of row´s and cells order for this sheet has to be like this due to other reasons.

So here is my problem:

There are 17 instances where row 1 (C1:AN1) Equals 1 while simultaneously Row 2 (C2:AN2) Equals 1 also.
There are 5 instances where row 1 (C1:AN1) Equals 2 while simultaneously Row 2 (C2:AN2) being 1. This relates to Agent 1

There are 13 instances where row 1 (C1:AN1) Equals 1 while simultaneously row Row 2 (C2:AN2) being 1 also.
There are 2 instances where row 1 (C1:AN1) Equals 2 while simultaneously row Row 2 (C2:AN2) being 1. This relates to Agent 1

The first parameter is always based on row 1 from the values inserted in cells C12 and C13
The second parameter changes based on the value in Cel B12 and B14.

Rows 2 to 9 only have the values "0" or 1"

I´m thinking a Sumifs formula with an itegrated Index formula, but I cant seem to get the sintaxe right.

Any help would be great.
Thanks

As a start,

In C13 >> =SUMPRODUCT(--($C$1:$AN$1=C$12),--($C2:$AN2=1)) >> copied over to D13

That´s great Jeff, thank you, but I need the formula to be flexible depending on the choice of agent.

This works great for a static range, but I need the formula to also inclue the variable value in cells B12.
So if I chane the agent, the formula would also change the value.

This is a small part of a bigger sheet. This formula will feed from a drop down list, where I can change the agent name in another sheet.

Thanks

A little clarification...

There are 17 instances where row 1 (C1:AN1) Equals 1 while simultaneously row Row 2 (C2:AN2) Equals 1 also.
There are 13 instances where row 1 (C1:AN1) Equals 1 while simultaneously row Row 2 (C2:AN2) being 1 also.

I'm having trouble seeing how these two statement would produce a different result...

The second statement does not follow with me either...

You´re right.

Apologies, the row is different. One refers to Agent 1, the other should reffer to Agent 2.

I meant to say:

There are 17 instances where row 1 (C1:AN1) Equals 1 while simultaneously row 2 (C2:AN2) Equals 1 also. (Agent 1)
There are 12 instances where row 1 (C1:AN1) Equals 1 while simultaneously row 3 (C3:AN3) equals 1 also. (Agent 2)

My idea is that is probably necessary an (index/match) formula inserted in the "sumproduct/count/sum" formula so it identifies which row to take the data from, depending on the agent name selection. But I just can´t find a proper way to write it...

Edit: I’m attaching the file again with proper values and syntax so the thread gets all accurate information.Apologies.

Perhaps

Please Login or Register  to view this content.

will help ?
I had to unmerge cells to get values into B12 and B14 - Avoid merge cells at all cost they are nothing but trouble

EDIT to be entered in C13

@ Pepe,

This is it! Fantastique! Thank you so much for the help guys.

Glad to help. Please do not quote entire posts if not necessary. They clutter threads - Thx