Filtering with "SUMPRODUCT()" raises "a value used in the formula is of the wrong data type"

[MC_Kay]

Hello together,
I'm stuck at a "SUMPRODUCT()" formula.
I want to use that formula as filter (VLOOKUP()) for several criteria. But stuck at a point I don't understand.

My sheet has a table with > 3 columns.

Column	contains	type	empty rows
A	timestamp	string	no
B	item-id	integer	no
C	state	string	some

I want to get the state over the time.

The formula I've used:

=SUMPRODUCT((A3:A100=A1)*(B3:B100=B1)*C3:C100)

but I got #VALUE! with the yellow square: "a value used in the formula is of the wrong data type"

What I tried:
Missing "()"

=SUMPRODUCT((A3:A100=A1)*(B3:B100=B1)*(C3:C100))

Still the same issue.

=SUMPRODUCT((A3:A100=A1)*(B3:B100=B1))

This returns 1

I was thinking: Maybe the values I gave in row 1 aren't good, so I tried to search by values inside of the database as reference:

=SUMPRODUCT((A3:A100=A4)*(B3:B100=B4)*C3:C100)

But with the same issue.

Than I've tried to search not in column C, but in B instead:

=SUMPRODUCT((A3:A100=A1)*(B3:B100=B1)*B3:B100)

and I got as result the vale I expected (Given in B1). But I want to get the value of column C and not B!

What I'm doing wrong here?

I hope you can help me

 

[RoryA]

If column C is a String, what do you hope to return by multiplying it?

You could simply use FILTER in 365, or something like:

=LOOKUP(2,1/(A3:A100=A1)/(B3:B100=B1),C3:C100)

in other versions.

 

[MC_Kay]

If column C is a String, what do you hope to return by multiplying it?

Oh. xD

I was thinking, that it'll work with everything and not numbers only.
But thank you. the formula you've posted works very well

 

[MC_Kay]

Sry for double post, but I didn't fount the "edit" button.

The first try was to get a number:
Column D to F containing number only and got the same issue:

=SUMPRODUCT((A3:A100=A1)*(B3:B100=B1)*D3:D100)

"a value used in the formula is of the wrong data type"

Why?

 

[RoryA]

Does the data start in row 3 or is that a header row?

 

[MC_Kay]

Does the data start in row 3 or is that a header row?

Rows:
1: criteria I want to filter for
2: header
3: first row that contains data
...
100. last row that contains data

That means, I've selected the ranges that contains data only (no headers).

 

[RoryA]

It would seem that there is something in column D that the formula doesn't like. Try:

=SUMPRODUCT((A3:A100=A1)*(B3:B100=B1),D3:D100)

and see if that works.

 

[MC_Kay]

It would seem that there is something in column D that the formula doesn't like.

Maybe, but all the values in column D are numbers. Ok, some are empty, but that shouldn't raise that error.

=SUMPRODUCT((A3:A100=A1)*(B3:B100=B1),D3:D100)

Yes, that worked.
When I use that on column C (with strings) I got 0 and not "#VALUE!" anymore

Thank you

 

[RoryA]

You can't use SUMPRODUCT with strings since its entire purpose in life is to multiply numbers, then add them up.